(Solved) – Insert Formula in Column C if Column B is not Empty


I have created a VBA macro in excel. It works just not exactly how I want it to.

I want the macro to insert a formula in each individual cell in range C1:C160 for each cell in range B2:B160 that is not empty

My code is as follows:

Sub Check()
    Dim dat As Variant
    Dim rng As Range
    Dim i As Long

    Set rng = Range("B2:B160")
    dat = rng
    For i = LBound(dat, 1) To UBound(dat, 1)
        If dat(i, 1) <> "" Then
            rng(i, 2).Formula = "=RIGHT(B2, LEN(B2)-12)"
        End If
End Sub

The problem is it inserts the same formula into each cell. The formula is not dynamic.
How can I make it dynamic so cell C3 has formula: “=RIGHT(B3, LEN(B3)-12) and so on and so forth.

Any tips would help thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *