Find Text and Replace with Formula using VBA

[Trikageon]

Hey all! I'm looking for a code in my macro that will search out a text in a given range and replace it with a formula. For example:

In column G, I want to find and replace every instance of the word "Total" (a stand-alone word, not part of a formula) with a formula similar to =SUMIF(O:O,"Total"&N17,G:G)

Now, I say similar to because, the N17 is a relative reference, which is effectually saying I want the word "Total," in this formula, to concatenate with the cell that's 2 rows up and 7 columns over from the cell that's being evaluated

Any ideas?

 

[Andrew Poulsom]

Record a macro while entering that formula. Excel will use the FormulaR1C1 property and the R1C1 reference style, which is what you need.

 

[Richard Schollar]

Try:

Dim rng As Range
 
With ActiveSheet.Range("G:G")
  Set rng = .Find(What:="Total",LookAt:=xlWhole,LookIn:=xlValues)
 
  If Not rng Is Nothing Then
    Do
      rng.FormulaR1C1 = "=SUMIF(C15,""Total""&R[-2]C[7],C7)"
      Set rng = .FindNext(rng)
    Loop While Not rng Is Nothing
  End If
End With

 

[Trikageon]

Ok, that worked great, except I'm getting an error at the end...it points to this when I debug:

Set rng = .FindNext(rng)

Also, what would I do if I also wanted to include column H?