Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find & Replace in VB macro
I have a macro that selects a range of cells and looks for the relative
column heading (i.e. !E or !G) and should replace it with the next sequential column header. Unfortunately, I can not get the IF statement to proceed to the next level once it does not find "!E" in the current selection. The first macro defines the selection of cells and then calls the afore mentioned Find & Replace macro. Here is my current code. If Selection.Find(What:="!E", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) Then Selection.Replace What:="!E", Replacement:="!F", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False ElseIf FocusFR = Selection.Find(What:="!F", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) Then Selection.Replace What:="!F", Replacement:="!G", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False I am not sure if the Find statement has to be in an equation or what. The goal is that the macro will find the current header, change it to the next one, and then go back to the previous macro and get the next selection criteria. |
#2
|
|||
|
|||
If there is nothing to replace, then it won't matter. You can drop that .find
portion and just use .replace. JackC wrote: I have a macro that selects a range of cells and looks for the relative column heading (i.e. !E or !G) and should replace it with the next sequential column header. Unfortunately, I can not get the IF statement to proceed to the next level once it does not find "!E" in the current selection. The first macro defines the selection of cells and then calls the afore mentioned Find & Replace macro. Here is my current code. If Selection.Find(What:="!E", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) Then Selection.Replace What:="!E", Replacement:="!F", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False ElseIf FocusFR = Selection.Find(What:="!F", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) Then Selection.Replace What:="!F", Replacement:="!G", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False I am not sure if the Find statement has to be in an equation or what. The goal is that the macro will find the current header, change it to the next one, and then go back to the previous macro and get the next selection criteria. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace - problem with automatically changing formatting | Excel Discussion (Misc queries) | |||
How do you find and replace a Wildcard character in Excel? | Excel Discussion (Misc queries) | |||
find and replace path name in Excel cells containing hyperlink | Excel Discussion (Misc queries) | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
find and replace | Excel Discussion (Misc queries) |