FormulaR1C1 and Variable.

**JapanDave** · 01-23-2012, 04:16 PM

No matter what I do I still can't get my head around variables and formulas.

I am trying to reference Row -117 by using a positive variable value.
The variable LR = 100 in this example, any help would be appreciated.

The top code is the straight formula.

.FormulaR1C1 = "=COUNTIF( R[-117]C:R[-1]C,""Yes"")"

.FormulaR1C1 = "=COUNTIF( R[" & -LR - 17 & "]C:R[-1]C,""Yes"")"

**Firefly2012** · 01-23-2012, 04:31 PM

and what seems to be the problem? What cell are you trying to enter this formula into?

**shg** · 01-23-2012, 04:31 PM

Your second example works fine for me ...

**JapanDave** · 01-23-2012, 04:57 PM

Hey shg, the second example runs fine, but it does not give the row offset value of -117, it gives me a value of -51???

Edit: I am placing the formula in Row 135 to count values from row 17 to 134.

**Firefly2012** · 01-23-2012, 05:02 PM

Originally Posted by Firefly2012

What cell are you trying to enter this formula into?

You are entering a formula with relative references, so I ask again, what cell are you entering this formula into? Row 168 by any chance?

**JapanDave** · 01-23-2012, 05:03 PM

It is in my last post.

**Firefly2012** · 01-23-2012, 05:06 PM

Then LR doesn't equal 100 when the line of code is executed - can you place a breakpoint on this line and run the code and then see what value LR is when this line is executed?

**shg** · 01-23-2012, 07:09 PM

The way I solve problems like this is to set a breakpoint at the line of interest, then copy the string to the Immediate window:

? "=COUNTIF( R[" & -LR - 17 & "]C:R[-1]C,""Yes"")"

Press Enter, and I saw:

=COUNTIF( R[-117]C:R[-1]C,"Yes")

... as expected. As I said, it worked fine for me, Dave. Want to post a simple workbook with an example that fails?

**JapanDave** · 01-24-2012, 06:23 AM

Originally Posted by Firefly2012

Then LR doesn't equal 100 when the line of code is executed

shg & Firefly, your right it does work, my math skills don't however. Firefly, the moment I read your post I new a few lines ahead of this line that I changed the LR variable value.

Ahh, I have at this piece of code too long.

@ shg, thanks for the tip on the Immediate window, I would never have thought to use that.

Thanks guys.
Dave