CODEWITHSUNDEEP
vbaexcel
Maciej
Maciej

Reputation: 47

Get address of cell in function .Find

I have code like below, I would like to get address of cell in which value is if value is found. Because if macro find existing value in SomeRange, I would like change value of this cell.

Range(SomeRange).Select
    Set a = Selection.Find(What:=Something, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

Upvotes: 1

Views: 80

Answers (1)

user4039065
user4039065

Reputation:

You need to guard against not finding anything.

dim a as range
Range(SomeRange).Select
on error resume next
Set a = Selection.Find(What:=Something, After:=ActiveCell, LookIn:=xlValues, _
   LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
   MatchCase:=False, SearchFormat:=False)
if not a is nothing then
     debug.print a.address(0, 0)
     a = "changed value"
end if

Upvotes: 4

Related Questions