PDA

View Full Version : Solved: Get The Replaced Cell's Address



dansam
05-19-2007, 11:12 AM
Hi everyone :hi: ,

I have a little problem here :

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

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


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

Help ! :think:


Best Regards,
Dan

Bob Phillips
05-19-2007, 11:38 AM
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

mdmackillop
05-19-2007, 11:39 AM
Hi Dan
Split the Replace from the Find

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