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

from:
"R[-4]"
to
"R[" & i1 & "]"

"Fred Holmes" wrote:

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

Works like a charm. Many thanks. What help topic should I look under
that explains this code? Or some other reference? I don't think I
would have found it on my own even with a whole lot of effort.

What type of variable is the argument of R[ ] ?

How does the code provided change the integer variable "i1" to the
correct variable type?

I tried to use the Str() function to change i1 to a string, and wrap
it in Ltrim(), but that didn't work.

Fred Holmes

On Fri, 9 Jan 2009 05:36:04 -0800, Joel
wrote:

from:
"R[-4]"
to
"R[" & i1 & "]"

"Fred Holmes" wrote:

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

"Fred Holmes" wrote:

Works like a charm. Many thanks. What help topic should I look under
that explains this code? Or some other reference? I don't think I
would have found it on my own even with a whole lot of effort.

A string in excel can consist of multiple items using an amphersand (&) to
connect pices of the string. Adding a variable which contains a number will
convert the number also to a string

a = 24
Message = "My Number is" & a

This will return

Message = "My Number is 24"




What type of variable is the argument of R[ ] ?

R[] is what they call an R1C1 address. Excel has both A1 addressing and
R1C1 address.

See on worksheet HELP : About cell and range references



How does the code provided change the integer variable "i1" to the
correct variable type?

I tried to use the Str() function to change i1 to a string, and wrap
it in Ltrim(), but that didn't work.

Fred Holmes

On Fri, 9 Jan 2009 05:36:04 -0800, Joel
wrote:

from:
"R[-4]"
to
"R[" & i1 & "]"

"Fred Holmes" wrote:

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