Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range with Relative R[1]C[1] notation
In a macro, I need to select a range of cells that I want to refer to with the R[1]C[1] style. I know the following works for a range from a relative Cell to an ABSOLUTE one: Range(ActiveCell, "G4").Select But for a range defined by TWO RELATIVE cells ? Say my active cell is now C4 and I want to select from A5 to F5; I am thinking of something like this: ActiiveCell.Range(R[1]C[-2], R[1]C[3]).Select (which does not work). Thank you so much for any help, as in past occasions. ACA -- aca ------------------------------------------------------------------------ aca's Profile: http://www.excelforum.com/member.php...o&userid=25933 View this thread: http://www.excelforum.com/showthread...hreadid=546246 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range with Relative R[1]C[1] notation
You can't use RC notation like that, you need to pass the numbers to a
Range, Cells or Offset property. ActiveCell.Offset(1, -2).Resize(, 6).Select -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "aca" wrote in message ... In a macro, I need to select a range of cells that I want to refer to with the R[1]C[1] style. I know the following works for a range from a relative Cell to an ABSOLUTE one: Range(ActiveCell, "G4").Select But for a range defined by TWO RELATIVE cells ? Say my active cell is now C4 and I want to select from A5 to F5; I am thinking of something like this: ActiiveCell.Range(R[1]C[-2], R[1]C[3]).Select (which does not work). Thank you so much for any help, as in past occasions. ACA -- aca ------------------------------------------------------------------------ aca's Profile: http://www.excelforum.com/member.php...o&userid=25933 View this thread: http://www.excelforum.com/showthread...hreadid=546246 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range with Relative R[1]C[1] notation
Thank you, Bob, for a quick reply. Your line works all right. But I would like to avoid using Offse because I want my ActiveCell to remain the same one (C4), as this lin is part of a search down column C which must go on, and it would tak longer if the Active cell hast to shift to column A at each find an then back to C to continue the search. Cannot I select A5:F5 while staying at C4 as my Active cell? But, hanks again. AC -- ac ----------------------------------------------------------------------- aca's Profile: http://www.excelforum.com/member.php...fo&userid=2593 View this thread: http://www.excelforum.com/showthread.php?threadid=54624 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range with Relative R[1]C[1] notation
No, if you want to select a larger range as you describe, then, by default,
when the selection is made, the first cell in the range is made the active cell. If you are concerned about speed, then you shouldn't be selecting at all. The fast way to seach down column C is Dim cell as Range, rng as Range, rng1 as Range set rng = Range(Cells(1,"C"),Cells(rows.count,"C").End(xlup) ) for each cell in rng set rng1 = cell.Offset(0,-2).Resize(1,6) ' now work with rng ' you see no time is spent selecting or changing the selection Next The sooner you quit selecting, the better your code is going to be. -- Regards, Tom Ogilvy "aca" wrote in message ... Thank you, Bob, for a quick reply. Your line works all right. But I would like to avoid using Offset because I want my ActiveCell to remain the same one (C4), as this line is part of a search down column C which must go on, and it would take longer if the Active cell hast to shift to column A at each find and then back to C to continue the search. Cannot I select A5:F5 while staying at C4 as my Active cell? But, hanks again. ACA -- aca ------------------------------------------------------------------------ aca's Profile: http://www.excelforum.com/member.php...o&userid=25933 View this thread: http://www.excelforum.com/showthread...hreadid=546246 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range with Relative R[1]C[1] notation
Thank you Tom; but I’m afraid that is too advanced for me (I don' really master DIM...); my macro with your lines -as ssuch- doesn’ work. Leave it at that; I cannot ask you to educate me from scratch now! But just in case you have nothing better to do, what I’m trying is (i a macro for book keeping) search the column of the expense items (C for rows headed “Subtotal ”; and for each of these, draw a line al across the page (at the top of the following row) to mark the beginnin of a new batch of items for the next subtotal. At present I do it like this (which is slow indeed) ----------------------------------------------------------- Do While ActiveCell < "Grand Total" ‘ this is the last row i my list Cells.Find(What:="Subtotal ", After:=ActiveCell,…).Activate ActiveCell.Offset(1,-2).Select. ActiveCell.Range("A:G").Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous… End With Loop ------------------------------------------------------------ But, as I said, don’t go into it if you’re busy. It’s up to me to lear DIM . Thanks. AC -- ac ----------------------------------------------------------------------- aca's Profile: http://www.excelforum.com/member.php...fo&userid=2593 View this thread: http://www.excelforum.com/showthread.php?threadid=54624 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range with Relative R[1]C[1] notation
Sub BBB()
Dim rng As Range, sAddr As String Set rng = Columns(3).Find(What:="Subtotal ", _ After:=Cells(Rows.Count, "C").End(xlUp), _ LookAt:=xlPart, LookIn:=xlValues, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address Do With rng.Offset(1, -2).Range( _ "A1:G1").Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Set rng = Columns(3).FindNext(rng) Loop While rng.Address < sAddr End If End Sub -- Regards, Tom Ogilvy "aca" wrote in message ... Thank you Tom; but I'm afraid that is too advanced for me (I don't really master DIM...); my macro with your lines -as ssuch- doesn't work. Leave it at that; I cannot ask you to educate me from scratch now! But just in case you have nothing better to do, what I'm trying is (in a macro for book keeping) search the column of the expense items (C) for rows headed "Subtotal "; and for each of these, draw a line all across the page (at the top of the following row) to mark the beginning of a new batch of items for the next subtotal. At present I do it like this (which is slow indeed) ----------------------------------------------------------- Do While ActiveCell < "Grand Total" ' this is the last row in my list Cells.Find(What:="Subtotal ", After:=ActiveCell,.).Activate ActiveCell.Offset(1,-2).Select. ActiveCell.Range("A:G").Select With Selection.Borders(xlEdgeTop) LineStyle = xlContinuous. End With Loop ------------------------------------------------------------ But, as I said, don't go into it if you're busy. It's up to me to learn DIM . Thanks. ACA -- aca ------------------------------------------------------------------------ aca's Profile: http://www.excelforum.com/member.php...o&userid=25933 View this thread: http://www.excelforum.com/showthread...hreadid=546246 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range with Relative R[1]C[1] notation
Tom, many, many thanks for going out of your way to help me, by spelling in ful detail the whole macro I need; I didn't expect such kindness and compliance, really. Bless you. ACA -- aca ------------------------------------------------------------------------ aca's Profile: http://www.excelforum.com/member.php...o&userid=25933 View this thread: http://www.excelforum.com/showthread...hreadid=546246 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining a range for a look up | Excel Programming | |||
Range R1C1 notation & the problem with my Macro | Excel Programming | |||
Range Notation for Excel Functions in VBA | Excel Programming | |||
Defining a Range | Excel Programming | |||
Using Range with R1C1 notation in a macro | Excel Programming |