| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro-replace
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
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 "puiuluipui" wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
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! "Mike H" wrote: 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 "puiuluipui" wrote: 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
Replace
Cells.Replace with Columns("L").Replace If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro-replace
It's perfect. Perfect!
Thanks allot! "Jacob Skaria" wrote: Replace Cells.Replace with Columns("L").Replace If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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" wrote: 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 --------------- Jacob Skaria "puiuluipui" wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replace Macro | Excel Discussion (Misc queries) | |||
| Replace Vlookup with a macro | Excel Discussion (Misc queries) | |||
| Macro to Find & Replace | Excel Worksheet Functions | |||
| replace macro? | Excel Worksheet Functions | |||
| Find & Replace in VB macro | Excel Discussion (Misc queries) | |||
Linear Mode
