Adding a value and formula to same cell

[craigg3]

Starting with cell "I9", that cell has a formula in it. I need a macro that will remove the formula from that cell, but leave the value while also adding a formula that will add cell "H9" to the value.
So, for example, if the formula in cell "I9" = 100, then I need the formula to be removed but keeping the value of 100 while also adding a formula that adds the value in cell "H9". So if cell "H9" has a value of 50, then the final value/formula in cell "I9" would be =100+H9....which would be 150 but do need the formula to link back to H9.

This would need to be done on the "I" column where the same row in column "F" <>""

There should only be about 40 or 50 rows that will need this code, but that may change at some point.

 

[Joe4]

Try this code:

Sub AddFormulas()
    
    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column I with data
    lr = Cells(Rows.Count, "I").End(xlUp).Row
    
'   Loop through all rows, starting with row 2
    For r = 2 To lr
'       See if column F is not blank
        If Cells(r, "F") <> "" Then
            Cells(r, "I").FormulaR1C1 = "=" & Cells(r, "I").Value & "+RC[-1]"
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[craigg3]

Try this code:

Sub AddFormulas()
   
    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column I with data
    lr = Cells(Rows.Count, "I").End(xlUp).Row
   
'   Loop through all rows, starting with row 2
    For r = 2 To lr
'       See if column F is not blank
        If Cells(r, "F") <> "" Then
            Cells(r, "I").FormulaR1C1 = "=" & Cells(r, "I").Value & "+RC[-1]"
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Joe, works great. Thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!