Simple find & replace question

[RonOliver]

Hi everyone!

I'm looking for a way to append a formula to another existing formula, both with a prefix and a suffix.

For example, if I have a bunch of cells with =SUM(*,*), with the asterisks being placeholders for other cells that are different every time, and I want to put them into =IF(SUM(*,*),1,0), how can I replace them all in one go? My original formula is VERY long and I wouldn't want to post it here, but this is basically what it boils down to. It should be really easy, but it's taking me a lot to do this.

Thanks!

 

[Alex Blakenburg]

If you are happy to use a macro this might work.
Note: please try it on a copy of your workbook

Sub updateformula()

    Dim rng As Range, arr As Variant
    Dim i As Long, j As Long
    
    Set rng = ActiveSheet.UsedRange
    arr = rng.Formula2
    
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            If Left(arr(i, j), 4) = "=SUM" Then
                arr(i, j) = "=IF(" & Mid(arr(i, j), 2) & ",1,0)"
            End If
        Next j
    Next i
    
    rng.Formula2 = arr

End Sub

 

[RoryA]

Manual option:
Replace = with zz
Replace SUM with IF(SUM
Replace ) with ),1,0)
Replace zz with =

 

[RonOliver]

If you are happy to use a macro this might work.
Note: please try it on a copy of your workbook

Sub updateformula()

    Dim rng As Range, arr As Variant
    Dim i As Long, j As Long
  
    Set rng = ActiveSheet.UsedRange
    arr = rng.Formula2
  
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            If Left(arr(i, j), 4) = "=SUM" Then
                arr(i, j) = "=IF(" & Mid(arr(i, j), 2) & ",1,0)"
            End If
        Next j
    Next i
  
    rng.Formula2 = arr

End Sub

Will try this shortly. I need to start learning VBA already. It looks like it's the only way to go when problems are a little too big.

 

[RonOliver]

Manual option:
Replace = with zz
Replace SUM with IF(SUM
Replace ) with ),1,0)
Replace zz with =

I love this! Didn't even think of replacing the equal sign. Will check and let you know if it worked for me!

 

[Alex Blakenburg]

Manual option:
Replace = with zz
Replace SUM with IF(SUM
Replace ) with ),1,0)
Replace zz with =

I thought of that but the wording "My original formula is VERY long" implied there might be more than one ")" in the formula.

 

[RoryA]

I interpreted that as just meaning there were a lot of individual cell references in there. If not, then this will create some interesting formulas...

 

[RonOliver]

I thought of that but the wording "My original formula is VERY long" implied there might be more than one ")" in the formula.

There are, but I found a non-repeating pattern within the formula that I can use. I just kept coming across the equal sign error and I didn't know how to work around it.

 

[RonOliver]

Thanks very much to both of you. You've saved me hours of work.

 

[Alex Blakenburg]

Glad Rory's Find & Replace worked for you. Perhaps you can mark his post #3 as the solution