I am trying to get the code to search for the replacement criteria and then loop a replacement sub until there are no more replacement criteria. The main reason I am doing it like this is the active cell reference is important for what the criteria is being replaced with. The main issue is my "acell" range is not finding anything and being set as nothing even though the criteria is in the sheet and I can't tell why (or I am not understanding the function correctly). If anything else looks off any help would be appreciated here.
VBA Code:
Sub looptest()
'trying to have the relpace sequence one cell at a time
Dim acell As Range
Dim cellAddress As Range
With Sheets("Template").Range("AA:Z")
Set acell = .Find(What:="Replace", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not acell Is Nothing Then
cellAddress = acell.Address
Do
'Have it select the cell it found the "replace" on and then call replacement sub then loop the process until no more replacement criteria are found
cellAddress.Select
replacement
Set acell = .FindNext(acell)
Loop While Not acell Is Nothing
End If
End With
End Sub
VBA Code:
Sub replacement()
'houses the logic to run the replacement sequence
Dim strRef As String
Dim LD As String
Let strRef = ActiveCell.Address
Let LD = Workbooks("test.xlsm").Sheets("test sheet").Range("M45")
Selection.Replace What:="Replace", replacement:="='[test.xlsm]MSRP'!" & strRef & "*(1-" & LD & ")", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
