Find the location of a cell that matches a string in the entire worksheet-CodePudding

Suppose I want to find the cell that contains the string "XYXY" in the entire worksheet (say it is in cells(100,35)) and change the value of the cell next to it in the next column (e.g. cells(100,36)). How can this be done efficiently?

I know of

    Set CellPV = ActiveSheet.Cells.Find(What:="XYXY", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

which does find the cell effectively, however does not show location/Range; when I use MsgBox(CellPV) , it gives me the value of the cell, as opposed to its location in the worksheet.

How can this be done?

CodePudding user response：

You don't need to know CellPV's address on the worksheet.

Just use Range.Offset:

If Not CellPV Is Nothing Then 'Test if Find was successful
   CellPV.Offset(,1).Value = "foo"
End If

If you really do want to know cellPVs address, then:

MsgBox CellPV.Address 'no parentheses here