R1C1 Cell reference Question

[Cordury]

Range("B6&quot.Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[2]),"" "",IF(ISBLANK(R[-1]C[2]),R[-1]C[1],IF(R[-1]C>0,R[-1]C,"" "&quot))"

I realize that R stands for Row and C for Column but I thought that cell A1 = R1C1. I imagine that the negative numbers have the formula move back or up.


Can someone translate this formula using the letter/number cell references? I can see that it is inserting a column and comparing other cells but I am just not sure which.
Thanks,
Cord

 

[xlbo]

R1C1 is relative to the current position
so if this is inserting in B6, then
R[-1]C[2]
would refer to D5 (one column up and 2 accross)

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Zathras]

The equvalent formula looks like this in cell A2:

  =IF(ISBLANK(C2)," ",IF(ISBLANK(C1),B1,IF(A1>0,A1," ")))

As to what it means, I haven't the foggiest.

BTW, R1C1 does not equal A1, it equals $A$1. You can switch between A1 and R1C1 notation with Tools/Options/General/R1C1 Reference Style to see the differences yourself.

 

[Cordury]

Thank you both. I knew how to change the view already but the VBA code was in R1C1. Just an FYI... This is for data dumped from some system into Excel and the columns and rows don't match up.

I appreciate your help!
Cord

 

[xlbo]

R1C1 is really only the style of notation
For fixed references, R1C1 = $A$1, R2C2 = $B$2 etc etc BUT, the way it was intended to be used was the relative referencing style

R[1]C[1] = the cell 1 up and 1 to the right of the cell being referred to. This is because you have to use variables / Offset functions to relatively reference A1 style notation

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Cordury]

Below is the entire code. I think I am running into a problem bc when the macro was recorded there was a fixed range. But the original file gets larger (more rows) as time goes by.

Thanks again,
Cord


Sub ()

Range("B6&quot.Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[2]),"" "",IF(ISBLANK(R[-1]C[2]),R[-1]C[1],IF(R[-1]C>0,R[-1]C,"" "&quot))"
Range("B6&quot.Select
Selection.Copy
Range("B6:B65536&quot.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B6:B65536&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A5:H5022&quot.Select
Selection.Sort Key1:=Range("B5&quot, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D43246&quot.Select
Selection.EntireRow.Delete
Range("A4:H1779&quot.Select
Selection.Sort Key1:=Range("B4&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub