Using variable in Excel formula in VBA statement

F

Fred Holmes

Excel 2000 VBA using R1C1 notation in the Workbook

I've written a simple "Journal" (check register) in a worksheet, and
I'm trying to program split checks. On the line below the individual
items in the split, in a different column, I want to total all of the
individual values in the split (state a total value for the check). I
want to generate the formula to do that with a VBA statement.

The following code runs OK and does *almost* what I want to do:

ActiveCell.Offset(i1, 3).FormulaR1C1 = "=""Total of check is $
""&FIXED(SUM(R[-4]C[3]:R[-1]C[3]),2)"

What I really need is for the "4" in "R[-4]" to not be a hard coded
number, but to be obtained from the value of an integer variable.

The name of the variable I'm using is "i1", and although it's a cell
reference in "normal" notation, it seems to work fine in all of the
other code in my macro. It is declared in a Dim statement:
Dim i1 as Integer
It works fine in the "Offset(i1, 3)" part of the above formula.

Simply substituting "-i1" for "-4" does not work. I've guessed at
some variations which also do not work.

When the code fails, I get no runtime error in the macro, but the
designated cell is (apparently) empty; no formula at all is placed in
the desingated cell.

Thanks for any help

Fred Holmes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top