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]

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

 

[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.

 

[aca]

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

 

[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

 

[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