CODEWITHSUNDEEP
excelvbafunction
Artiom Bic
Artiom Bic

Reputation: 11

Trying to input variable into a function in VBA

I am trying to create a VBA code that inputs the sum function into the cell:

LR = Cells(Rows.Count, 1).End(xlUp).Row
LX = LR - 2
Range("B" & LX).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-LX]C:R[-4]C)"

But when I input LX into the sum function, it doesn't recognize it as a variable. How can I fix this? Because LX depends on the initial number of rows which can vary.

I don't know how to fix this. I get the run-time error '1004'.

Upvotes: 0

Views: 67

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

As states in the comments above, you need to take LX out of the parenthesis of the formula.

Also, you should try to avoide using Select and ActiveCell, and instead use fully qualified refence objects:

Modified code:

Set Sht = ThisWorkbook.Worksheets("Sheet1") ' <-- modify "Sheet1" to your sheet's name
With Sht
    LR = .Cells(.Rows.Count, 1).End(xlUp).Row
    LX = LR - 2
    .Range("B" & LX).FormulaR1C1 = "=SUM(R[-" & LX & "]C:R[-4]C)"
End With

Upvotes: 2

Related Questions