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 Next 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!