Syntax for FormulaR1C1 Using Variables

**Gard5096** · 01-08-2013, 01:41 PM

I am new to using the FormulaR1C1 style of syntax, and am running into some (hopefully simple) problems. I am using variables, but when I input them into the formula, I believe it is searching relative cells rather than the column assigned to the variable.

The function I am trying to produce in every active row for column "iVariable1" is as follows:

With Range(Cells(2, iVariable1), Cells(LR, iVariable1))

IF(AND(Cells(2, iVariable5) <>"", Cells(2, iVariable5) > Cells(2, iVariable6)), "TRUE", "FALSE")

End With

Here is the coding I have:

Please Login or Register  to view this content.

*Note: Each of the iVariable's represents a column.

Is there a way to have the formula grab the column I am looking for, rather than it finding an irrelevant column?

**MrShorty** · 01-08-2013, 01:51 PM

I believe it is searching relative cells rather than the column assigned to the variable.

In R1C1 notation, the brackets [] denote relative references. A number without brackets denotes an absolute reference. So

RC[-1] denotes the cell in the same row and one column to the left of the cell containing the formula
R1C[-1] denotes the cell in row 1 (absolute) and one column to the left of the cell containing the formula
R1C1 denotes the cell at the intersection of row 1 and column 1 no matter where the formula is located (absolute reference)

It sounds like you want an absolute reference, so you need to drop the brackets and write the reference in absolute syntax.