CODEWITHSUNDEEP
excelvba
Ivan
Ivan

Reputation: 1

Change SAME Text in formulas in Multiple Columns with text in Different Column but Same Row AND repeat this across several rows

I have formulas in several columns from Column ATE to Column AVG

And these same formulas are in Rows 2165 to 2700

All the formulas have this text: ANG

Now, for each row in above range - I'd like to replace ANG with different text that is found in Same Row in Column AVH

For example, ANG in Row 2165 in Columns ATE to AVG (wherever there is formula with ANG) will be replaced with the text from Column AVH in same row 2165 -> this text for example is ABC

Then ANG in Row 2166 in Columns ATE to AVG (wherever there is formula with ANG) will be replaced with the text from Column AVH in same row 2166 -> this text for example is DEF

And so on, until the same is done for last row, which is Row 2700

I have found many codes (such as below) that replace the specific value with another specified value. However, I would like to specify or manually select the range and then only specify the text ANG and then specify the column where the VBA code will find the replacement text, rather than specifying the actual replacement text for each row in code

Sub TEST()
Dim c As Range
Application.ScreenUpdating = False
On Error Resume Next
For Each c In Sheets(“Version 3”).Range(ATE2165,AVG2700).SpecialCells(xlFormulas)
   c.Formula = Replace(c.Formula, “ANG, ****so here I’d like the 
     code to find ANG in above range and replace with 3 letters 
     in Column AVH for that same row and continue same for all rows in above range***)
Next c
Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 29

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Something like this should work:

Sub TEST()
    Dim c As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each c In Sheets("Version 3").Range("ATE2165:AVG2700").SpecialCells(xlFormulas)
        c.Formula = Replace(c.Formula, "ANG", c.Parent.Cells(c.Row, "AVH").Value)
    Next c
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Related Questions