Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro edits won't work
The first 3 commands of this macro work fine. Not knowing much about VBA, to
try to add 2 more commands, I copied and pasted a working command twice and substituted the characters I wanted to manipulate in commands 4 and 5. Commands 4 and 5 don't work. How can I troubleshoot this and see where is the problem so I can correct it. Tx for your help. The macro is as follows (hopefully it is readable in this forum)" Sub Second_Macro_Rate_Comparisons() ' ' Change_Cellular_in_parentheses Macro ' Macro recorded 3/4/2005 by Edits ' ' Cells.Replace What:=" (Cellular)", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" GSM", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" - ", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:="(", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro edits won't work
This worked for me... I just removed some of the default values...
Sub Second_Macro_Rate_Comparisons() Cells.Replace What:=" (Cellular)", Replacement:="-Cellular", LookAt:=xlPart Cells.Replace What:=" GSM", Replacement:="-Cellular", LookAt:=xlPart Cells.Replace What:=" - ", Replacement:="-", LookAt:=xlPart Cells.Replace What:="(", Replacement:="-", LookAt:=xlPart Cells.Replace What:=")", Replacement:="", LookAt:=xlPart End Sub -- HTH... Jim Thomlinson "Tenacity" wrote: The first 3 commands of this macro work fine. Not knowing much about VBA, to try to add 2 more commands, I copied and pasted a working command twice and substituted the characters I wanted to manipulate in commands 4 and 5. Commands 4 and 5 don't work. How can I troubleshoot this and see where is the problem so I can correct it. Tx for your help. The macro is as follows (hopefully it is readable in this forum)" Sub Second_Macro_Rate_Comparisons() ' ' Change_Cellular_in_parentheses Macro ' Macro recorded 3/4/2005 by Edits ' ' Cells.Replace What:=" (Cellular)", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" GSM", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" - ", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:="(", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro edits won't work
Tx.
For some reason, the macro is not finding the ( or ). At first I thought maybe ( is not the ( character, but when I do a Find command, it finds the cell containing the ( just fine. For some reason, the macro is not changing the ( or ) as requested. Any suggestions? "Jim Thomlinson" wrote: This worked for me... I just removed some of the default values... Sub Second_Macro_Rate_Comparisons() Cells.Replace What:=" (Cellular)", Replacement:="-Cellular", LookAt:=xlPart Cells.Replace What:=" GSM", Replacement:="-Cellular", LookAt:=xlPart Cells.Replace What:=" - ", Replacement:="-", LookAt:=xlPart Cells.Replace What:="(", Replacement:="-", LookAt:=xlPart Cells.Replace What:=")", Replacement:="", LookAt:=xlPart End Sub -- HTH... Jim Thomlinson "Tenacity" wrote: The first 3 commands of this macro work fine. Not knowing much about VBA, to try to add 2 more commands, I copied and pasted a working command twice and substituted the characters I wanted to manipulate in commands 4 and 5. Commands 4 and 5 don't work. How can I troubleshoot this and see where is the problem so I can correct it. Tx for your help. The macro is as follows (hopefully it is readable in this forum)" Sub Second_Macro_Rate_Comparisons() ' ' Change_Cellular_in_parentheses Macro ' Macro recorded 3/4/2005 by Edits ' ' Cells.Replace What:=" (Cellular)", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" GSM", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" - ", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:="(", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro edits won't work
By any chance are the brackets part of a cell format and not an actual
character within the cell. My guess is that you need to just change the formatting of the cells. -- HTH... Jim Thomlinson "Tenacity" wrote: Tx. For some reason, the macro is not finding the ( or ). At first I thought maybe ( is not the ( character, but when I do a Find command, it finds the cell containing the ( just fine. For some reason, the macro is not changing the ( or ) as requested. Any suggestions? "Jim Thomlinson" wrote: This worked for me... I just removed some of the default values... Sub Second_Macro_Rate_Comparisons() Cells.Replace What:=" (Cellular)", Replacement:="-Cellular", LookAt:=xlPart Cells.Replace What:=" GSM", Replacement:="-Cellular", LookAt:=xlPart Cells.Replace What:=" - ", Replacement:="-", LookAt:=xlPart Cells.Replace What:="(", Replacement:="-", LookAt:=xlPart Cells.Replace What:=")", Replacement:="", LookAt:=xlPart End Sub -- HTH... Jim Thomlinson "Tenacity" wrote: The first 3 commands of this macro work fine. Not knowing much about VBA, to try to add 2 more commands, I copied and pasted a working command twice and substituted the characters I wanted to manipulate in commands 4 and 5. Commands 4 and 5 don't work. How can I troubleshoot this and see where is the problem so I can correct it. Tx for your help. The macro is as follows (hopefully it is readable in this forum)" Sub Second_Macro_Rate_Comparisons() ' ' Change_Cellular_in_parentheses Macro ' Macro recorded 3/4/2005 by Edits ' ' Cells.Replace What:=" (Cellular)", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" GSM", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" - ", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:="(", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro edits won't work
Right on! They should have been text, but whoever entered them somehow made
them as part of a formula. Formatting the entire column to Text worked. Many thanks. "Jim Thomlinson" wrote: By any chance are the brackets part of a cell format and not an actual character within the cell. My guess is that you need to just change the formatting of the cells. -- HTH... Jim Thomlinson "Tenacity" wrote: Tx. For some reason, the macro is not finding the ( or ). At first I thought maybe ( is not the ( character, but when I do a Find command, it finds the cell containing the ( just fine. For some reason, the macro is not changing the ( or ) as requested. Any suggestions? "Jim Thomlinson" wrote: This worked for me... I just removed some of the default values... Sub Second_Macro_Rate_Comparisons() Cells.Replace What:=" (Cellular)", Replacement:="-Cellular", LookAt:=xlPart Cells.Replace What:=" GSM", Replacement:="-Cellular", LookAt:=xlPart Cells.Replace What:=" - ", Replacement:="-", LookAt:=xlPart Cells.Replace What:="(", Replacement:="-", LookAt:=xlPart Cells.Replace What:=")", Replacement:="", LookAt:=xlPart End Sub -- HTH... Jim Thomlinson "Tenacity" wrote: The first 3 commands of this macro work fine. Not knowing much about VBA, to try to add 2 more commands, I copied and pasted a working command twice and substituted the characters I wanted to manipulate in commands 4 and 5. Commands 4 and 5 don't work. How can I troubleshoot this and see where is the problem so I can correct it. Tx for your help. The macro is as follows (hopefully it is readable in this forum)" Sub Second_Macro_Rate_Comparisons() ' ' Change_Cellular_in_parentheses Macro ' Macro recorded 3/4/2005 by Edits ' ' Cells.Replace What:=" (Cellular)", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" GSM", Replacement:="-Cellular", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:=" - ", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:="(", Replacement:="-", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert "Find dialog box" edits into macro | Excel Discussion (Misc queries) | |||
Macro to Protect the Whole Workbook and Allow Range Edits | Excel Discussion (Misc queries) | |||
Can I allow Additions but not Edits? | Excel Discussion (Misc queries) | |||
Footer edits | Excel Discussion (Misc queries) | |||
Limiting edits | Excel Discussion (Misc queries) |