use of integer in .FormulaR1C1

[caroline]

hello,
I used integer A and B in formula such as
..FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C"
and it works fine
How can I use the same principle for the following:
..FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))"
[" & A &"] does not seem to be recognised

PS: it is important that I use .FormulaR1C1 as my user needs to see the
formula used.

Thanks a lot.

 

[Paul C]

you are combining a string and a number which is giving you the issue

try
.FormulaR1C1 = "=INDEX(RangeX,[" & CSTR(A) &"],MATCH(YearEnd,Years,0))"
the CSTR(A) converts A to a string.

 

[caroline]

No it did not work. i must be doing something wrong
--
caroline

you are combining a string and a number which is giving you the issue

try
.FormulaR1C1 = "=INDEX(RangeX,[" & CSTR(A) &"],MATCH(YearEnd,Years,0))"
the CSTR(A) converts A to a string.
--
If this helps, please remember to click yes.

hello,
I used integer A and B in formula such as
.FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C"
and it works fine
How can I use the same principle for the following:
.FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))"
[" & A &"] does not seem to be recognised

PS: it is important that I use .FormulaR1C1 as my user needs to see the
formula used.

Thanks a lot.

 

[Dave Peterson]

Try dropping the []'s from your formular1c1 string.

If that doesn't help, create the formula manually in the worksheet cell.

Then turn on R1C1 reference style (if it's not turned on).

If you can't get your code to create this formula, then copy the formula and
paste into your followup message.

In this case (because you're using named ranges), there won't be a difference
between R1C1 reference formular1c1 and the A1 reference formula. (Well, unless
I guessed wrong at what you were doing.)

hello,
I used integer A and B in formula such as
.FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C"
and it works fine
How can I use the same principle for the following:
.FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))"
[" & A &"] does not seem to be recognised

PS: it is important that I use .FormulaR1C1 as my user needs to see the
formula used.

Thanks a lot.

 

[caroline]

Dropping the []'s from the formular1c1 string was the answer.
Thanks a million

--
caroline

Try dropping the []'s from your formular1c1 string.

If that doesn't help, create the formula manually in the worksheet cell.

Then turn on R1C1 reference style (if it's not turned on).

If you can't get your code to create this formula, then copy the formula and
paste into your followup message.

In this case (because you're using named ranges), there won't be a difference
between R1C1 reference formular1c1 and the A1 reference formula. (Well, unless
I guessed wrong at what you were doing.)

hello,
I used integer A and B in formula such as
.FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C"
and it works fine
How can I use the same principle for the following:
.FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))"
[" & A &"] does not seem to be recognised

PS: it is important that I use .FormulaR1C1 as my user needs to see the
formula used.

Thanks a lot.