Defining Range with Relative R[1]C[1] notation

[aca]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[aca]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[aca]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[aca]

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