Macro-replace

  • Thread starter Thread starter puiuluipui
  • Start date Start date
P

puiuluipui

Hi, i need a macro to replace:
Jim with Atlanta
John with Las Vegas
Mary with Chicago
......
This criteria to be inside macro.
Can this be done?
Thanks!
 
Hi,

You don't need a macro for this you can simply do a search/replace but as
you asked for a macro, try this

Sub Sonic()
Dim RepString As Variant
Dim FindString As Variant
FindString = "Jim,John,Mary"
RepString = "Atlanta,Las Vegas,Chicago"
s = Split(FindString, ",")
t = Split(RepString, ",")
For x = 0 To UBound(s)
Cells.Replace What:=s(x), Replacement:=t(x), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
End Sub

Mike
 
Try the below...

Sub Macro1()

Dim intTemp As Integer
Dim arrFind As Variant, arrReplace As Variant

arrFind = Array("Jim", "John", "Mary")
arrReplace = Array("Atlanta", "Las Vegas", "Chicago")

For intTemp = 0 To UBound(arrFind)
Cells.Replace What:=arrFind(intTemp), _
Replacement:=arrReplace(intTemp), _
LookAt:=xlWhole, SearchOrder:=xlByRows
Next

End Sub

;If you are looking for strings within the cell replace xlWhole with xlPart

If this post helps click Yes
 
Hi, my first post was a little bit wrong.
I need to change a few cities with a name:

Atlanta; New York; Denver to Jim
Las Vegas, Detroit; Washington to John
Chicago; Ohio, Los Angeles to Mary

Sorry for my first post.
Can this be done?
Thanks!
 
Hi, my first post was a little bit wrong.
I need to change a few cities with a name:

Atlanta; New York; Denver to Jim
Las Vegas, Detroit; Washington to John
Chicago; Ohio, Los Angeles to Mary

Sorry for my first post.
Can this be done?
Thanks!
 
You can add those conditions to the below two arrays...like below and then
use the same macro...

Atlanta; New York; Denver to Jim
Las Vegas, Detroit; Washington to John
Chicago; Ohio, Los Angeles to Mary

arrFind = Array("Atlanta", "New York" , "Denver", "Las Vegas", "Chicago")
arrReplace = Array("Jim", "Jim","Jim", "John", "Mary")


If this post helps click Yes
 
It's working. Thanks!
But i have so many entries i have to change, not just these three examples.
And it wiould become confusing. Can i do something like this?

arrFind = Array("Atlanta", "New York" , "Denver")
arrReplace = Array("Jim")

arrFind = Array("Las Vegas", "Detroit", "Washington ")
arrReplace = Array("John")

arrFind = Array("Chicago", "Ohio", "Los Angeles ")
arrReplace = Array("Mary")

And i need the macro to replace only in Column L range (L:L)
Can this be done?
Thanks!
 
Try the below..In the below example find and replace texts are mentioned in
Sheet3 in the same workbook and the range is A1:B10. Adjust to suit

Dim intTemp As Integer, arrFindReplace As Variant
arrFindReplace = Worksheets("Sheet3").Range("A1:B10")

For intTemp = 1 To UBound(arrFindReplace)
If Trim(arrFindReplace(intTemp, 1)) <> "" Then
Cells.Replace What:=arrFindReplace(intTemp, 1), _
Replacement:= arrFindReplace(intTemp, 2), _
LookAt:=xlWhole, SearchOrder:=xlByRows
End If
Next

If this post helps click Yes
 
This is better than everything. Thanks allot!
The problem is that the macro is changing everything in sheet1 (active
sheet). I need this last code, but to change text only in L column (L:L).
Can this be done?
Thanks!
 
Back
Top