use of integer in .FormulaR1C1

  • Thread starter Thread starter caroline
  • Start date Start date
C

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.
 
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.
 
No it did not work. i must be doing something wrong
--
caroline


Paul C said:
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.


caroline said:
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
 
Dropping the []'s from the formular1c1 string was the answer.
Thanks a million

--
caroline


Dave Peterson said:
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.
Click to expand...
Click to expand...
 
Back
Top