Macro find replace in formula

Post your recorded code - don't forget code tags! We'll then edit the code for you so it works everywhere.

If you delete these 2 lines the macro will then work on what ever the selected cells are. The first line ties it to the sheet RecapPro the second line ties it to a specific range. So remove those 2 lines and it will work anywhere (assuming you've selected some cells)

 Sheets("RecapPro").Select
    Range("AO3:BL206").Select

Ahh i see so you have lots of workbooks each with this sheet name and range?

This below will loop through all open worksheets (in all workbooks) looking for sheets named RecapPro and perfom the find / replace

Sub Macro1()
Dim wks as worksheet
Dim Wkb as workbook    

for each wkb in workbooks
for each wks in wkb.worksheets
  if wks.name = "RecapPro" then
    wks.Range("AO3:BL206").Replace What:="""AND(""", Replacement:= _
        "AND((INDIRECT(""AL""&ROW())=""1"");", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 end if
next wks
next wkb
End Sub

Ah sorry didn't even look at what you were trying to find and replace, assumed it worked . This is a bit more complicated as looks like you are trying to build a link based on location of cell. Can you post a desensitized example spreadsheet for us to play with?
ta
bren

As you want to build a replace string based on the cell found address you need to cycle through each found cell with findnext and build the string.

Sub Macro1()
Dim wks As Worksheet
Dim Wkb As Workbook
Dim lastcell As Range
Dim seachrange As Range
Dim cellfound As Variant

Dim firstaddress As String

Dim findtext As String
Dim replacetext As String

findstring = "AND("

For Each Wkb In Workbooks
For Each wks In Wkb.Worksheets
  If wks.Name = "RecapPro" Then
    
    Set searchRange = wks.Range("AO3:BL206")
    Set lastcell = searchRange.Cells(searchRange.Cells.Count)
    
    Set cellfound = searchRange.Find(What:=findstring, After:=lastcell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not cellfound Is Nothing Then
firstaddress = cellfound.Address

 
Do

Set cellfound = searchRange.FindNext(cellfound)
replacetext = "AND(INDIRECT(" & Chr(34) & "AL" & cellfound.Row & Chr(34) & " )=1,"

cellfound.Formula = Replace(cellfound.Formula, findstring, replacetext)

Loop Until cellfound.Address = firstaddress
  
End If

 End If
Next wks
Next Wkb

Set lastcell = Nothing
Set seachrange = Nothing
Set cellfound = Nothing

End Sub

If you are happy please can you mark your thread solved?
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved


and if I helped please consider some * add rep.

ta
bren