memorize cells position

[Guest]

Hi,
My question is simply enough, but searching in the previous posts I didn't
find any similar one.
So, in my excel sheet after looking for a string with this command:

Cells.Find(What:="REPORT DATE", LookIn:=xlFormulas, LookAt:=xlPart).Activate

I need to put into a variable the first coordinate of row and column that
satisfy my find criteria.
Thanks in advance.

 

[Norman Jones]

Hi Andrea,

Try something like:

'=============>>
Public Sub Tester001()
Dim SH As Worksheet
Dim Rng As Range
Dim sAdd As String
Dim iRow As Long
Dim iCol As Long

Set SH = ActiveSheet '<<==== CHANGE
With SH
Set Rng = .Cells.Find(What:="REPORT DATE", _
After:=.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not Rng Is Nothing Then
With Rng
sAdd = .Address(0, 0)
iRow = .Row
iCol = .Column
End With
End If
End Sub
'<<=============