Loop - Find and Replace

I am trying to make a find and replace that will find ABS which is any time ABS() is used and replace it with a relative formula ...
I tried using the find and replace to replace all...which works but not in tandem with R1C1 style formulas.

Sub FReplace()
    Cells.Replace What:="*ABS*", Replacement:= _
        "=IFERROR(IF(AND(RC[-6]=0,RC[-5]<>0),1,ABS(RC[-5]/RC[-6])),0)", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

So my next thought was a loop... as making it the found cell active first and doing a replace or entering the formula will work and have it go until it is past the last row count...
Could not get a loop to work but here is a code that works, and replaces the next cell in order in which they appear but requires hitting play over and over... I have thousands in various columns to replace...

Sub FReplaceActive()
    Cells.Find(What:="*ABS*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(AND(RC[-6]=0,RC[-5]<>0),1,ABS(RC[-5]/RC[-6])),0)"
End Sub

Fingers are crossed -

Cheers

Currently on all sheets - there are several columns - never goes below 300 Rows! so I tried Do While Rows.Count < 301 but that seemed to not work... currently sitting with the module open and setting a water bottle on a pen that is holding F5 down while it runs through each sheet... lol - has to be a better way

Total guess here but rather than use cells and step through each one separately can you use this syntax

Worksheets("Sheet1").Columns("A").find

Instead

You didn't post the loop code. You should be using something like the code from HELP:

Example
This example finds all cells in the range A1:A500 on
worksheet one that contain the value 2 and changes it to 5.

 
With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

If you have multiple sheets and multiple columns in each sheet, you probably need a couple of For Each... loops to select the sheet then select the column(s) within that sheet the finally the code above. (3 level nesting)

Not at home - but will try to wrap it up in something similar - My loop code was garbage so figured I would show the replace method hoping for a suggestion - will play with that code and let you know what I come up with and/or if I hit errors

Cheers

So finally got around to this after a weekend of hell dealing with a Transmission...

This is the final (Working) for what I need - though occasionally once finished I get an error

Object Variable or With block not set

Not entirely sure what I am missing... I added an extra set C = Nothing just to clear it but even still happens (Not always and the code finishes so... idk?)

protonLeah, used your quoted with statement - Thanks for that!

Sub FReplace_S()

    Dim WS_Count As Integer
    Dim I As Integer
    Dim lastRow As Long
    Dim lastColumn As Integer
    Dim EndCol As String
    Dim FindWhat As String
    Dim ReplaceWithWhat As String
   
    FindWhat = "*ABS*"
    ReplaceWithWhat = "=IFERROR(IF(AND(RC[-6]=0,RC[-5]<>0),1,ABS(RC[-5]/RC[-6])),0)"
    
    lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    EndCol = Replace(Cells(1, lastColumn).Address(False, False), "1", "")

    WS_Count = ActiveWorkbook.Worksheets.Count
    
    For I = 1 To WS_Count

        With Worksheets(I).Range("A1:" & EndCol & lastRow)
            Set c = .Find(FindWhat, LookIn:=xlFormulas)
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    c.Value = ReplaceWithWhat
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
    
    Next I

    Set c = Nothing

End Sub

Will leave it open for another day for error troubleshooting - otherwise - protonLeah and Crooza - Appreciate the assist!

So looks like that block error is an issue - Open to suggestions on where I am going wrong -

This:

Loop While Not c Is Nothing And c.Address <> firstAddress

is actually incorrect, even though it's what the Help files have always shown. Since both conditions are evaluated for And, if c is Nothing, trying to access c.Address will raise an error.

Given that you are replacing one string containing "ABS" with another string containing "ABS", c shouldn't ever be Nothing inside the loop (though you should be using the .FormulaR1C1 property, not the .Value). How reproducible is the error?

Extremely reproducible - It happens with another one I attempted to do which was Findwhat= " $L35 " and ReplaceWithWhat was another formula which would not have had that locked reference in it.

It happens every time the condition is met and the replace takes effect... You can add the code to a blank workbook... add something in any cell then make sure the findwhat is able to locate in that book...

Open for suggestions...

If you are replacing all instances with another string that does not contain the original search string, you should only use:

Loop While Not c Is Nothing

If you are replacing with a string that contains the same text, you should only use:

Loop While c.Address <> firstAddress

Works with Loop While Not c Is Nothing, seem to get the same block error with the Loop While C.Address <> firstAddress

So as long as I do not attempt to replace with similar string this is good to go - Thanks RomperStomper!