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.

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