CODEWITHSUNDEEP
excelvbaexcel-formula
Janet Delgado
Janet Delgado

Reputation: 13

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

Upvotes: 1

Views: 728

Answers (3)

JvdV
JvdV

Reputation: 75840

The formula can adjust itself when you apply it to a whole range. You were only interested in non-empty cells so maybe try the following:

Sub Check()

With Sheet1 'Change according to your sheet's CodeName
    .Range("B2:B160").SpecialCells(xlCellTypeConstants).Offset(0, 1).Formula = "=RIGHT(B2,LEN(B2)-12)"
End With

End Sub

As you can see I'd recommend to at least make the Range object refer to a certain sheet explicitly! Otherwise you run the risk of using the implicit ActiveSheet which might be incorrect.

Upvotes: 1

HuynhCam
HuynhCam

Reputation: 26

As @braX, but edit a little for you need:

rng(i, 2).Formula = "=RIGHT(B" & (i + 1) & ", LEN(B" & (i + 1) & ")-12)"

Upvotes: 1

braX
braX

Reputation: 11745

Build a string using a variable as the row like this

rng(i, 2).Formula = "=RIGHT(B" & i & ", LEN(B" & i & ")-12)"

That may not be exactly what you need, but you see how string concatenation works that way, right? Modify that to suit your needs.

Upvotes: 1

Related Questions