Find specific string in range of cells

[Ian]

I am trying to "future-proof" some code.

I currently have some code which returns the first and last dates in a
specific column. This is fine, except that the column may change (was A, is
now B, may change again).

I have to modify the code as it is currently specific to column A.

What is the easiest way to fing the column with Date in row 1? I know I can
specify a range (eg A1:G1) then loop through to match the string, but is
there a better way?

 

[Guest]

something like this

Dim rngDate As Range

Set rngDate = Range("A1:G1").Find(What:="Date", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngDate Is Nothing Then
MsgBox "Date was not found"
Else
MsgBox rngDate.Address
'your code here
End If

 

[Ian]

I only needed the column number so changing rngDate.Address to
rngDate.Column got me sorted.

Thanks for your help. I knew there had to be an easier way