R1C1 syntax

Can someone please help me convert and understand the below formula in R1C1, I am struggling a bit with this. Thank you.

 Const myEpilepsy As String = "=IF(SUMPRODUCT(--(Panel!$B$2:$B$6713=$Q$5),--(Panel!$C$2:$C$6713<=$R$5),--(Panel!$D$2:$D$6713>$R$5)),VLOOKUP($R$5,Panel!$C$2:$E$6713,3,1),""No"")"

Here is my attempt:

 ActiveCell.FormulaR1C1 = _
Const myEpilepsy As String = "=IF(SUMPRODUCT(--(Panel!$R[2]$R[2]:$R[6713]$R[2]=Annovar!$R[5]$C[17]),--(Panel!$R[2]$C[3]:$R[6713]$C[3]<=Annovar!$R[5]$C[18]),--(Panel!$R[2]$C[4]:$R[6713]$C[4]>Annovar!$R[5]$C[18])),VLOOKUP(Annovar!$R[5]$C[18],Panel!$R[2]$C[3]:$R[6713]$C[5],3,1),""No"")"

R1C1 notation doesn't use $

R refers to the row containing the formula (a relative reference)

Ry refers to row y (an absolute reference)

R[+-y] refers to y rows above/below the cell containing the formula (a relative reference)

References to columns are symmetric.

So if I follow, A1 would be:

R[1]C[1] - R[1]C[2] = A1 - B1

R1C1 = A1

R[2]C[1], R[-1]C[-1] = moving from A2 to A1

R[2]C[3], R[-1]C[-3] = moving from C2 to A1

Thanks.

R1C1 refers to A1, always and forever, regardless of where it appears, yes. All the others depend on where the formula is entered; they are relative references.

Assume you are in C5, that cell is = R5C3. It will always reference that cell its like a $C$5

If you are in C5 and put in an equation =R[1]C[1] . It will give you the value of D6 (One row positive/down and One column positive/right)
If you are in C5 and put in an equation =R[-1]C[-1] . It will give you the value of B4 (One row negative/up and One column negative/left)
If you are in C5 and put in an equation = R3C2. It will give you the value in B3 (Row3 column2)

(Row,Column)
A1 = 1,1
B2 = 2,2
C10 = 10,3

Panel!$B$2:$B$6713

Should be

Panel!R2C2:R6713C2


Row2 = Row2
Col2 = B
Col3 = C
Col4 = D

Why do you want to convert the formula to R1C1?

R[1]C[1] - R[1]C[2] = A1 - B1

If it will help, I would point out that the relative reference R[1]C[1] can never refer to A1. A1 (R1C1) is the leftmost, topmost cell in a sheet. There cannot be a cell 1 row above and 1 column to the left of A1 (R1C1). It is not possible to have a relative R1C1 reference with positive offsets (positive numbers inside the []'s) that refers to A1.

If that made sense to you, then you are well on your way to understanding R1C1 references. If that created more confusion, then you still have a ways to go in understanding how these work.

R[2]C[1], R[-1]C[-1] = moving from A2 to A1
R[2]C[3], R[-1]C[-3] = moving from C2 to A1

Another thing I would point out is that R1C1 references, when copied, do not "change" -- the formula text looks exactly the same as it did in the original cell.

If it will help, I would point out that the relative reference R[1]C[1] can never refer to A1.

Umm -- yes it can, if it's in the bottom right corner of the sheet.

Originally Posted by shg

Umm -- yes it can, if it's in the bottom right corner of the sheet.

Having never had a spreadsheet large enough to even consider this possibility, I always thought it was impossible. I did not realize these references could "wrap around". With Winnie the Pooh, I declare myself "foolish and deluded, and a bear of no brain at all."

I would modify my conclusion to, if you can understand how the wrap around works, you are well on your way to understanding how R1C1 relative references work.

That's wrong, R1C1 is not needed for VBA.

The formula may not be working but converting it to R1C1 isn't going to make any difference.

PS Did you every try changing $R$5, $Q$5 etc do $R5, $Q5 etc as I suggested - think I even posted code with those references changed.

Have you tried stepping through it?

You can do that by selecting a cell with the formula in it, preferably one that's not giving the correct result, and going to the Formulas tab and clicking Evaluate Formula.

It was brought to my attention that R1C1 was needed for VBA.

Yes and no. If your code says Range(...).FormulaR1C1 = then, yes! it will require a formula in R1C1 format. If, however, it says .Formula = then, no, it can be a formula as you would type it in the cell. In both cases, give or take. If you have any constants in quotes, you will need to double up the quotes.

In the original post, I think you should forget about the Const and just have:

ActiveCell.Formula = _
      "=IF(SUMPRODUCT(--(Panel!$B$2:$B$6713=$Q$5),--(Panel!$C$2:$C$6713<=$R$5),--(Panel!$D$2:$D$6713>$R$5)),VLOOKUP($R$5,Panel!$C$2:$E$6713,3,1),""No"")"

Note that VLOOKUP with fourth parameter of 0 or FALSE is required for an EXACT match.


Regards, TMS

The formula that works is completely different from the formula in the code.

I don't understand. They are two completely different formulae. Nothing to do with whether or not it's on the same sheet. You've changed COUNTIFS to SUMPRODUCT and you have even changed the cells you compare.

No surprise it doesn't give the same result.

Regards, TMS

You can't just change from COUNTIFS to SUMPRODUCT. The structure of the functions are different.

SUMPRODUCT and COUNTIFS are not interchangeable.

You might be able to substitute one for the other, but not without making significant changes.

How exactly are you changing the formula?

I'd suggest you look up the Help for both formulae and compare the structures. Also, check the parameters for VLOOKUP while you're at it.

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.