Find string then replace with string 1
Posted by Andonny on February 22, 2001 4:13 AM
Hi,
I am looking for a macro which finds "Andonny" in column B and then replaces it with "Richard". I found a macro which replaces every "Andonny" with "Richard".
I like it to replace the first "Andonny" found but not the others which follow.
This is the code:
Sub Replace_name()
Dim RowNum As Integer
Dim LastRow As Integer
With Worksheets("Sheet1")
LastRow = .UsedRange.Rows.Count
For RowNum = LastRow To 1 Step -1
If Range("B" & RowNum) = "Andonny" Then
Range("B" & RowNum) = "Richard"
End If
Next RowNum
End With
End Sub
Posted by dan on February 22, 2001 4:33 AM
What's wrong with the regular find/replace function in Excel??
Posted by Andonny on February 22, 2001 5:26 AM
Hi,
I tried that but it replaces every Andonny it can find. I only need to replace the first Andonny it finds. Also I have more names I need to replace and a macro would be much quicker for me then repeating these steps for every Name I like to change. Sorry about that.
Andonny
Posted by David Hawley on February 22, 2001 6:56 AM
Andonny, try this.
Sheets("Sheet1").Columns(2).Find _
(What:="Andonny", After:=[B1]).Replace _
What:="Andonny", Replacement:="Richard"
This will only replace the first occurence.
Dave
OzGrid Business Applications
Posted by Andonny on February 22, 2001 7:18 AM
Thanks a million. Works just perfect.
Andonny
Posted by Celia on February 22, 2001 7:32 AM
Unless "Andonny" is in B1 and also appears in another cell in column B. In which case the second occurrence will be replaced.
To handle this possibility use instead :-
Sheets("Sheet1").Columns(2).Find _
(What:="Andonny", After:=[B65536]).Replace _
What:="Andonny", Replacement:="Richard"
Celia
Posted by David Hawley on February 22, 2001 8:22 PM
On my tests my code works !
I put "Andonny" is ten random cells within Column B. I then run my code and ONLY the first instance of "Andonny" is replaced with "Richard".
Why doesn't it work for you guys ?
Dave
OzGrid Business Applications
Posted by Celia on February 22, 2001 10:43 PM
Re: On my tests my code works !
Dave
Read the message again - you are running your test on a different situation.
Try it if one of your ten cells in column B that contains "Andonny" happens to be cell B1. You will find that ONLY the second occurrence of "Andonny" will be replaced with "Richard".
This is, of course, logical if you use "After:=[B1]" in the code.
Solution :- use instead "After:=[B65536]"
Celia
Posted by David Hawley on February 22, 2001 11:47 PM
Re: On my tests my code works !
Oh! ok I see what you mean.
Dave
OzGrid Business Applications
Posted by Andonny on February 23, 2001 2:24 AM
Hi Celia,
Thank you very much for you kind advice.
Andonny
