0

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'.

Shai Rado
  • 33,032
  • 6
  • 29
  • 51
Artiom Bic
  • 11
  • 2
  • 1
    Strings have to be concatenated by `&` (not a plus (+)) --> `ActiveCell.FormulaR1C1 = "=SUM(R[-" & LX & "]C:R[-4]C)"`. Additional tip: Read [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424). – Ike Jan 16 '23 at 12:10
  • Let's say your data is in `A1:B20` i.e. `LR = 20`. Where do you want to put the formula and what should it sum up e.g. `B22` and `B2:B20`? – VBasic2008 Jan 16 '23 at 13:34

1 Answers1

2

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
Shai Rado
  • 33,032
  • 6
  • 29
  • 51