Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Dash
I have a macro that finds and replaces "-" with text ("REP") in a particular
column (in this example, C). My worksheet looks like this: A B C abc 123 abc - 321 - abc 456 - a-c 654 - The macro works fine except for rows that happen to also have a dash in column A. Maybe my macro isn't specifying column C exactly? I used the Record Macro option... Sub ReplaceDash() Columns("I:I").Select Range("I7").Activate Selection.Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Could someone please shed some light? Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Dash
For this example, "I" in the macro would be "C".
"aria" wrote: I have a macro that finds and replaces "-" with text ("REP") in a particular column (in this example, C). My worksheet looks like this: A B C abc 123 abc - 321 - abc 456 - a-c 654 - The macro works fine except for rows that happen to also have a dash in column A. Maybe my macro isn't specifying column C exactly? I used the Record Macro option... Sub ReplaceDash() Columns("I:I").Select Range("I7").Activate Selection.Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Could someone please shed some light? Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Dash
you need to specify column A if you want your serach to cover it too...
Columns("A:C").Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False "aria" wrote: For this example, "I" in the macro would be "C". "aria" wrote: I have a macro that finds and replaces "-" with text ("REP") in a particular column (in this example, C). My worksheet looks like this: A B C abc 123 abc - 321 - abc 456 - a-c 654 - The macro works fine except for rows that happen to also have a dash in column A. Maybe my macro isn't specifying column C exactly? I used the Record Macro option... Sub ReplaceDash() Columns("I:I").Select Range("I7").Activate Selection.Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Could someone please shed some light? Thanks in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Dash
On Mar 24, 7:46*pm, aria wrote:
I have a macro that finds and replaces "-" with text ("REP") in a particular column (in this example, C). *My worksheet looks like this: A * * * B * * * C abc * *123 * *abc - * * * *321 * *- abc * *456 * *- a-c * * 654 * *- The macro works fine except for rows that happen to also have a dash in column A. *Maybe my macro isn't specifying column C exactly? *I used the Record Macro option... Sub ReplaceDash() * * Columns("I:I").Select * * Range("I7").Activate * * Selection.Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ * * * * SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ * * * * ReplaceFormat:=False End Sub Could someone please shed some light? *Thanks in advance! Hi, Do you want to replace dash in column A also? Anant |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Dash
Hi! Thanks for your response.
I actually only want it to cover column C but the macro as it is runs through other columns. For instance, my actual worksheet has 19 columns and the macro is going through 14 of them doing the replace. "Patrick Molloy" wrote: you need to specify column A if you want your serach to cover it too... Columns("A:C").Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False "aria" wrote: For this example, "I" in the macro would be "C". "aria" wrote: I have a macro that finds and replaces "-" with text ("REP") in a particular column (in this example, C). My worksheet looks like this: A B C abc 123 abc - 321 - abc 456 - a-c 654 - The macro works fine except for rows that happen to also have a dash in column A. Maybe my macro isn't specifying column C exactly? I used the Record Macro option... Sub ReplaceDash() Columns("I:I").Select Range("I7").Activate Selection.Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Could someone please shed some light? Thanks in advance! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Dash
What did you mean when you said the "macro works fine except for rows that
happen to also have a dash in column A"? When I run the macro, it replaces all of the dashes in Column C no matter what is in Column A on that row, which is what the macro is set up to do (although I'm not sure why you are activating C7 after selecting the whole column). Perhaps if you mention exactly what is supposed to be happening, or what is not happening the way you expect it to be happening, that might help us in figuring out your exact problem. Rick "aria" wrote in message ... I have a macro that finds and replaces "-" with text ("REP") in a particular column (in this example, C). My worksheet looks like this: A B C abc 123 abc - 321 - abc 456 - a-c 654 - The macro works fine except for rows that happen to also have a dash in column A. Maybe my macro isn't specifying column C exactly? I used the Record Macro option... Sub ReplaceDash() Columns("I:I").Select Range("I7").Activate Selection.Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Could someone please shed some light? Thanks in advance! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Dash
Not on my system it doesn't. This macro (yours with the I's replaced by
C's)... Sub ReplaceDash() Columns("C:C").Select Range("C7").Activate Selection.Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub replaces only the dashes in Column C... no other column is affected. Rick "aria" wrote in message ... Hi! Thanks for your response. I actually only want it to cover column C but the macro as it is runs through other columns. For instance, my actual worksheet has 19 columns and the macro is going through 14 of them doing the replace. "Patrick Molloy" wrote: you need to specify column A if you want your serach to cover it too... Columns("A:C").Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False "aria" wrote: For this example, "I" in the macro would be "C". "aria" wrote: I have a macro that finds and replaces "-" with text ("REP") in a particular column (in this example, C). My worksheet looks like this: A B C abc 123 abc - 321 - abc 456 - a-c 654 - The macro works fine except for rows that happen to also have a dash in column A. Maybe my macro isn't specifying column C exactly? I used the Record Macro option... Sub ReplaceDash() Columns("I:I").Select Range("I7").Activate Selection.Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Could someone please shed some light? Thanks in advance! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Dash
I found that the macro was getting thrown off by some merged cells somewhere
at the beginning of the very lengthy worksheet. After I fixed that the macro stayed in one column instead of moving out into the others. Thanks to everyone who took the time to respond! "aria" wrote: I have a macro that finds and replaces "-" with text ("REP") in a particular column (in this example, C). My worksheet looks like this: A B C abc 123 abc - 321 - abc 456 - a-c 654 - The macro works fine except for rows that happen to also have a dash in column A. Maybe my macro isn't specifying column C exactly? I used the Record Macro option... Sub ReplaceDash() Columns("I:I").Select Range("I7").Activate Selection.Replace What:="-", Replacement:="REP", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Could someone please shed some light? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace Comma's with Dash | Excel Worksheet Functions | |||
Find And Replace a Dash | Excel Discussion (Misc queries) | |||
Replace Comma with Dash | Excel Worksheet Functions | |||
replace dash in a number sequence | Excel Worksheet Functions | |||
Replace Error with a Dash | Excel Worksheet Functions |