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