VBA Excel formula error
 

Hi,
I'm trying to use a variable in a formula and it's not working. Here is the
formula:

Formula #1
ActiveCell.FormulaR1C1 = "=rc[x]-rc[2]"

The value of x is determined in a select case statement. For example
depending on the case selection I want the formula to say:

Formula #2
ActiveCell.FormulaR1C1 = "rc[-17]-rc[2]"

Formula #2 works but Formula #1 doesn't. How can I use a variable in #1 and
make it work?

Thanks much,
--
TomD

VBA Excel formula error
 

if x is inside the quotes it is treated as text, not as your variable - do
this instead:
ActiveCell.FormulaR1C1 = "=rc[" & x & "]-rc[2]"

"TomD" wrote:

Hi,
I'm trying to use a variable in a formula and it's not working. Here is the
formula:

Formula #1
ActiveCell.FormulaR1C1 = "=rc[x]-rc[2]"

The value of x is determined in a select case statement. For example
depending on the case selection I want the formula to say:

Formula #2
ActiveCell.FormulaR1C1 = "rc[-17]-rc[2]"

Formula #2 works but Formula #1 doesn't. How can I use a variable in #1 and
make it work?

Thanks much,
--
TomD