R1C1 Style VBA Code

Hi All,

Does anyone know how to reference Ranges in r1c1 reference style

For example
in A1 Style

Range("A1:A12).select

How would i translate this in r1c1 style in code.

Chet

Hi.

doesnt r1c1 notation refer to a single cell at a time and not ranges?

trental

R1C1 can refer to ranges in formulas, however, you can't use these types of strings with the Range method (you can only use single cells, like Trental says). The only way I can see around it is using a named range:
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1:R12C1"
Range("test").Select
ActiveWorkbook.Names("test").Delete
which is pretty yuk

nice work icjackson!

R1C1 can refer to ranges in formulas, however, you can't use these types of strings with the Range method (you can only use single cells, like Trental says). The only way I can see around it is using a named range:
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1:R12C1"
Range("test").Select
ActiveWorkbook.Names("test").Delete
which is pretty yuk


Thank you for the above advice. How do you use the range in a SumIf function within VBA? "=SUMIF(R[5]C[-2]:R[17]C[1],RC[-7],R[5]C[1]:R[17]C[1])"

How do you use the range in a SumIf function within VBA? "=SUMIF(R[5]C[-2]:R[17]C[1],RC[-7],R[5]C[1]:R[17]C[1])"

Could you please try to explain us what you want to achieve... :confused:
Why would you use/work in R1C1 notation ?

And let me lead you to some other threads about this issue...
Thread 1 (http://www.visualbasicforum.com/showthread.php?t=112863&highlight=r1c1)
Thread 2 (http://www.visualbasicforum.com/showthread.php?t=70252&highlight=r1c1) ;)
Thread 3 (http://www.visualbasicforum.com/showthread.php?t=85500&highlight=r1c1)
Thread 4 (http://www.visualbasicforum.com/showthread.php?t=113593&highlight=r1c1)

Thank you for your response. I love all the assistance I have received from this forum. I used RC notation because I created a macro to record the SUMIF function. I created a user form which adds a row when a record is entered that includes a field for project code. I want a single cell to add up all of the hours for that record based on the project code. In excel it would be =sumif(projectsandhoursrange, project code, hoursrange). I don't know how to refer to range of cells using VB. Additionally, every time a row is inserted the projectshours range moves down one row as well since it's located under the projects. I am VBA novice learning from a book so it would be great if anyone could provide the code to accomplish this.

Thank you for your response. I love all the assistance I have received from this forum. I used RC notation because I created a macro to record the SUMIF function. I created a user form which adds a row when a record is entered that includes a field for project code. I want a single cell to add up all of the hours for that record based on the project code. In excel it would be =sumif(projectsandhoursrange, project code, hoursrange). I don't know how to refer to range of cells using VB. Additionally, every time a row is inserted the projectshours range moves down one row as well since it's located under the projects. I am VBA novice learning from a book so it would be great if anyone could provide the code to accomplish this.
This might point you in the right direction.

TJ

I had the intention to reply earlier but seing TJ's answer i realized i did
misread you question so i had to quit...
Since TJ did already a nice job i can only add these two variants :
(based upon TJ's example)

'using a worksheetfunction in stead of a worksheet formula.
'(thus only a value in the cell and no formula)
.Cells(myRow, 3).Value = WorksheetFunction.SumIf(.Range("A2:A" & myRow), .Range("A" & myRow), .Range("B2:B" & myRow))
'and as a regular style build formula (because of easier to read)
.Cells(myRow, 3).Formula = "=SumIf($A$2:$A" & myRow & ", $A" & myRow & ", $B$2:$B" & myRow & ")"