Solved: Get The Replaced Cell's Address

[dansam]

Hi everyone ,

I have a little problem here :

I use the following code to make replacements in cells using macro :

[VBA] Cells.Replace What:="Sample Text", Replacement:="Sample Text2", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[/VBA]

I works fine for me but how to select or get the address of the cell where the replacement has made. Any Idea ??

Help !


Best Regards,
Dan

[Bob Phillips]

[vba]

Dim oCell As Range
Set oCell = Cells.Find("Sample Text")
If Not oCell Is Nothing Then
oCell.Value = "Sample Text2"
MsgBox oCell.Address
End If
[/vba]

[mdmackillop]

Hi Dan
Split the Replace from the Find

[VBA]Dim c As Range
Set c = Cells.Find(What:="Sample Text", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, _
SearchFormat:=False)
c = "Sample Text2"
MsgBox c.Address

[/VBA]