Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 Notation
Hi! I'm a little puzzled why Excel macro recording in some situations is
using R1C1 notation. Is there any benefit using that notation type? And, as I've never really worked with that notation, it sometimes give me problems. Especially when trying to use variables to describe a range. Does a 'translation'-description or other help exist somewhere? Haven't been able to find one! Thanks in advance Birger |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 Notation
Brym,
I personally find the R1C1 notation much easier if you are using variables. For instance,if I creating a formula that sums this row, column A to the column immediately to the left of this column I simply do ActiveCell.FormulaR1C1 = "=SUM(RC1:RC[-1])" whereas in A1 notation I need With ActiveCell .Formula = "=SUM(A" & .Row & ":" & Chr(.Column - 1 + 64) & .Row & ")" End With and it gets even more complex. -- HTH Bob Phillips "brym" wrote in message ... Hi! I'm a little puzzled why Excel macro recording in some situations is using R1C1 notation. Is there any benefit using that notation type? And, as I've never really worked with that notation, it sometimes give me problems. Especially when trying to use variables to describe a range. Does a 'translation'-description or other help exist somewhere? Haven't been able to find one! Thanks in advance Birger |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 Notation
Hi Bob! I see what you mean. But I rarely uses formulas like the ones
mentioned here. Writing vba I find the new notation easier to work with, and thats where I've had some problems interpretating macro-rec. code into my vba. Probably because I'm not familiar with it. It seem to me as if the R1C1 is best in writing formulas but not writing vba! Anyhow, I'd love to read some examples, best if they compare the two techniques. Know any places? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 Notation
Brym,
Sorry, I don't know where you can find what you want, but going back to the original topic, I also find R1C1 better in other ways. For instance Range("A1") is nice and straightforward, but Cells(1,1) is more useful if you want to use variables again (Cells(myRow, myCol)) or even a compromise Cells(1,"A"). I rarely use A1 notation, as I usr variable in which case I use R1C1 or I use named ranges. -- HTH Bob Phillips "brym" wrote in message ... Hi Bob! I see what you mean. But I rarely uses formulas like the ones mentioned here. Writing vba I find the new notation easier to work with, and thats where I've had some problems interpretating macro-rec. code into my vba. Probably because I'm not familiar with it. It seem to me as if the R1C1 is best in writing formulas but not writing vba! Anyhow, I'd love to read some examples, best if they compare the two techniques. Know any places? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 Notation
Just to add:
The FormulaR1C1 property can be used with A1-style range formulas: Sub Tester() Range("B10").FormulaR1C1 = "=RC[-1]" Range("K3").FormulaR1C1 = Range("B10").FormulaR1C1 Range("K4").Formula = Range("B10").Formula '<- not optimal End Sub Range("B10").FormulaR1C1 = "=RC[-1]" -Puts a formula in B10 relative to itself. Range("K3").FormulaR1C1 = Range("B10").FormulaR1C1 -Copies the formula from B10 to K3, maintaining relative integrity as if it was copied and pasted from the clipboard. Range("K4").Formula = Range("B10").Formula -Copies the formula from B10 to K3 but does not adjust the references. This is equivalent to copying the formula from the formula bar and then pasting it into the destination. Probably not desired. I think R1C1 notation takes some extra detective work to use. To me, this: Range("A2").Formula = "=K2*5" Range("X15:X25").FormulaR1C1 = Range("A1").FormulaR1C1 is easier to figure out than: Range("A2").FormulaR1C1 = "=RC[9]*5" 'etc IMO the strength of R1C1 style in VBA comes into play when formulas should be relative to a range. Then if a column is inserted into the spreadsheet, you'd have to adjust all of the A1-style formulas in VBA, which could be a royal pain in the a**. Like Bob said, if formulas are using variables and are relative, then that's probably the strongest case for R1C1 style. However, if no variables are being inserted into formulas, why not just store them on the spreadsheet and use the FormulaR1C1 property to write them to wherever. This would protect the integrity if a column is inserted in the destination sheet, because all that would be necessary would be to insert a column in the formula sheet too to accommodate the adjustment. Tim "brym" wrote in message ... Hi Bob! I see what you mean. But I rarely uses formulas like the ones mentioned here. Writing vba I find the new notation easier to work with, and thats where I've had some problems interpretating macro-rec. code into my vba. Probably because I'm not familiar with it. It seem to me as if the R1C1 is best in writing formulas but not writing vba! Anyhow, I'd love to read some examples, best if they compare the two techniques. Know any places? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
R1C1 | Excel Discussion (Misc queries) | |||
help on Converting R1C1 to A1 and A1 to R1C1..tia sa2 | Excel Discussion (Misc queries) | |||
help on Converting R1C1 to A1 and A1 to R1C1..tia sa2 | Excel Worksheet Functions | |||
How do I change sheet notation from R1C1 style to A1 style in XL 2 | Setting up and Configuration of Excel | |||
R1C1 | Excel Discussion (Misc queries) |