Macro-replace

[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!

 

[Mike H]

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

 

[Jacob Skaria]

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

 

[puiuluipui]

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!

 

[puiuluipui]

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!

 

[Jacob Skaria]

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

 

[puiuluipui]

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!

 

[Jacob Skaria]

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

 

[puiuluipui]

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!

 

[Jacob Skaria]

Replace
Cells.Replace
with
Columns("L").Replace

If this post helps click Yes

 

[puiuluipui]

It's perfect. Perfect!
Thanks allot!

Replace
Cells.Replace
with
Columns("L").Replace

If this post helps click Yes