Cell selection based on a criterion

[pt3]

Hi,

I am new to VBA and struggling with a piece of code; any help would be much appreciated!

I am attempting to write some code which instructs the selection of a cell, within a range, whereby the cell to select is one row lower than the column header (row 1 of the column), if the column header matches a date in a fixed cell of another sheet (in the code attempt below, the fixed cell, which contains the date, is cell N1 in "Sheet1").

My attempt:

Sub test()
Dim Found As Range, LookFor As String
LookFor = Sheets("Sheet1").Range("N1").Value
Set Found = Sheets("Sheet1").Range("A1:H1").Find(what:=LookFor)
If Found Is Nothing Then
MsgBox "Not found"
Else
Range(Found.Offset(1, 0)).Select
End If
End Sub

Thanks in advance.

 

[RoryA]

If I understood your description, and we are dealing with two different sheets, then I think you want:

Range(Found.Offset(1, 0).Address).Select

 

[wigi]

Hello there

If you are talking about 2 sheets, why do you have twice Sheets("Sheet1") in the code?

 

[pt3]

Thanks for the reply. You are correct- in my example code the reference cell and range are on the same sheet ("Sheet1"). I think the issue may be the line 'LookFor = Sheets("Sheet1").Range("N1").Value' - the macro does not seem to pick up the date in cell N1 from this command.

 

[RoryA]

Try changing this:

Dim Found As Range, LookFor As String

to this:

Dim Found As Range, LookFor As Date

 

[pt3]

This is superb Rory- it now works. Can I ask what the 'Address' addition instructs, as per you first response to my thread.

Many thanks.

 

[RoryA]

It returns the address of the found cell as text - e.g. "A1"
It's only necessary if the found cell is on a different sheet to the one that is active. If not, you can just use:

Found.Offset(1, 0).Select

 

[pt3]

Makes sense- thanks for the help.