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.
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
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