Incorrect formula created
 

' given weekOffset = 1
Range("B2").Select
ActiveCell.FormulaR1C1 = "=A2+" & weekOffset
' produces a formula in excel as:
' ='A2'+1
' i was expecting something like: =A2+1

Incorrect formula created
 

I noticed a formula in the CONCATENATE() function uses RC[index] reference.
I gave it a shot and it worked. I don't know what RC[] means, is this a safe
solution?

ActiveCell.FormulaR1C1 = "=RC[-1]+" & weekOffset

"Amil" wrote:

' given weekOffset = 1
Range("B2").Select
ActiveCell.FormulaR1C1 = "=A2+" & weekOffset
' produces a formula in excel as:
' ='A2'+1
' i was expecting something like: =A2+1

Incorrect formula created
 

Amil,

R1C1 notation uses row and column numers instead of A1 which uses
Alpha-numeric references

an "R" or "C" without a number refers to "this row" or "this column"
absolute numbers after R or C refer to absolute referferences line R1C1 =
$A$1
numbers in brackets [ ] after R or C are relative references to the active
cell (or the cell containing the formula.

If cell C5 = A1 + B1
than in R1C1 it would be
R5C3 = R[-4]C[-2] + R[-4]C[-2]

Because R1C1 methods use numbers - it is easier to use variables in code to
define formulas, ranges, etc.

--
steveB

Remove "AYN" from email to respond
"Amil" wrote in message
...
I noticed a formula in the CONCATENATE() function uses RC[index] reference.
I gave it a shot and it worked. I don't know what RC[] means, is this a
safe
solution?

ActiveCell.FormulaR1C1 = "=RC[-1]+" & weekOffset

"Amil" wrote:

' given weekOffset = 1
Range("B2").Select
ActiveCell.FormulaR1C1 = "=A2+" & weekOffset
' produces a formula in excel as:
' ='A2'+1
' i was expecting something like: =A2+1

Incorrect formula created
 

You've just given me a lesson on a very important concept in VBA! I do VBA
stuff but usually just rely on macros. Very good input.

"STEVE BELL" wrote:

Amil,

R1C1 notation uses row and column numers instead of A1 which uses
Alpha-numeric references

an "R" or "C" without a number refers to "this row" or "this column"
absolute numbers after R or C refer to absolute referferences line R1C1 =
$A$1
numbers in brackets [ ] after R or C are relative references to the active
cell (or the cell containing the formula.

If cell C5 = A1 + B1
than in R1C1 it would be
R5C3 = R[-4]C[-2] + R[-4]C[-2]

Because R1C1 methods use numbers - it is easier to use variables in code to
define formulas, ranges, etc.

--
steveB

Remove "AYN" from email to respond
"Amil" wrote in message
...
I noticed a formula in the CONCATENATE() function uses RC[index] reference.
I gave it a shot and it worked. I don't know what RC[] means, is this a
safe
solution?

ActiveCell.FormulaR1C1 = "=RC[-1]+" & weekOffset

"Amil" wrote:

' given weekOffset = 1
Range("B2").Select
ActiveCell.FormulaR1C1 = "=A2+" & weekOffset
' produces a formula in excel as:
' ='A2'+1
' i was expecting something like: =A2+1

Incorrect formula created
 

Amil,

You're Welcome...

Keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"Amil" wrote in message
...
You've just given me a lesson on a very important concept in VBA! I do
VBA
stuff but usually just rely on macros. Very good input.

"STEVE BELL" wrote:

Amil,

R1C1 notation uses row and column numers instead of A1 which uses
Alpha-numeric references

an "R" or "C" without a number refers to "this row" or "this column"
absolute numbers after R or C refer to absolute referferences line R1C1 =
$A$1
numbers in brackets [ ] after R or C are relative references to the
active
cell (or the cell containing the formula.

If cell C5 = A1 + B1
than in R1C1 it would be
R5C3 = R[-4]C[-2] + R[-4]C[-2]

Because R1C1 methods use numbers - it is easier to use variables in code
to
define formulas, ranges, etc.

--
steveB

Remove "AYN" from email to respond
"Amil" wrote in message
...
I noticed a formula in the CONCATENATE() function uses RC[index]
reference.
I gave it a shot and it worked. I don't know what RC[] means, is this
a
safe
solution?

ActiveCell.FormulaR1C1 = "=RC[-1]+" & weekOffset

"Amil" wrote:

' given weekOffset = 1
Range("B2").Select
ActiveCell.FormulaR1C1 = "=A2+" & weekOffset
' produces a formula in excel as:
' ='A2'+1
' i was expecting something like: =A2+1

Incorrect formula created
 

You original problem is easily corrected by using the correct syntax:

' given weekOffset = 1
Range("B2").Select
ActiveCell.Formula = "=A2+" & weekOffset

When using Formula us A1 notation. When using FormulaR1C1 use R1C1
notation.

--
Regards,
Tom Ogilvy

"Amil" wrote in message
...
You've just given me a lesson on a very important concept in VBA! I do
VBA
stuff but usually just rely on macros. Very good input.

"STEVE BELL" wrote:

Amil,

R1C1 notation uses row and column numers instead of A1 which uses
Alpha-numeric references

an "R" or "C" without a number refers to "this row" or "this column"
absolute numbers after R or C refer to absolute referferences line R1C1
=
$A$1
numbers in brackets [ ] after R or C are relative references to the
active
cell (or the cell containing the formula.

If cell C5 = A1 + B1
than in R1C1 it would be
R5C3 = R[-4]C[-2] + R[-4]C[-2]

Because R1C1 methods use numbers - it is easier to use variables in code
to
define formulas, ranges, etc.

--
steveB

Remove "AYN" from email to respond
"Amil" wrote in message
...
I noticed a formula in the CONCATENATE() function uses RC[index]
reference.
I gave it a shot and it worked. I don't know what RC[] means, is this
a
safe
solution?

ActiveCell.FormulaR1C1 = "=RC[-1]+" & weekOffset

"Amil" wrote:

' given weekOffset = 1
Range("B2").Select
ActiveCell.FormulaR1C1 = "=A2+" & weekOffset
' produces a formula in excel as:
' ='A2'+1
' i was expecting something like: =A2+1