GOING TO A PARTICULAR CELL BASED ON LOOKUP CRITERIA

  • Thread starter Thread starter Hillheader
  • Start date Start date
H

Hillheader

Hi

Can someone suggest how I can get a spreadsheet to open (or at least write a
macro to automatically move) to a particlar cell?

I have a range of dates in columns C to CS and data in rows (13 to 20) below
those dates. Using the "Today" function I have todays date in Cell A5 and
ideally would like to open the spreadsheet to row 13 for todays date. If this
is not possible, I would click to click a macro button with "go to today" on
it and move automatically to the correct cell.

If someone could suggest how either of these would be possible it would be
greatly appreciated.

Thanks in advance
 
So, are your dates in C12:CS12 ? Do you want to jump to C13 today,
then D13 tomorrow and E13 the next day etc, according to the value in
A5?

You could use a HYPERLINK formula to do this (perhaps in B5 saying
"jump"), but I need to know which row your pre-filled dates are in so
that I can match with A5 - please let me know.

Hope this helps.

Pete
 
Private Sub Workbook_Open()
Dim mpCol As Long

On Error Resume Next
mpCol = Application.Match(CLng(Range("A5").Value), Rows(10), 0)
On Error GoTo 0

If mpCol > 0 Then Cells(13, mpCol).Select
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code
 
Hi Bob

Again thanks for the prompt reply.

I've copied it as you said but it does not appear to do anything when the
file is saved and reopened. I'm working on Excel 2002. Does that make a
difference? If so, sincere apologies. I should have included that in the
initial post.

Thanks once more
 
or so you don't have to put in today's date in cell a5
mpCol = Application.Match(CLng(date), Rows(5), 0)
 
Okay, put this formula wherever it suits you:

=HYPERLINK("#"&ADDRESS(13,MATCH(TODAY(),C$10:CS$10,0)+2),"Go to
Today")

It will give you the message "Go to Today" in the cell, and if you
click on it the cursor will jump to row 13 under the appropriate date.

Hope this helps.

Pete
 
Cheers Pete

That works perfectly!!!

Thanks to Bob and Don also. Genuinely appreciated


Dave
 
Back
Top