Using R1C1 in VB to select a range

I've seen a lot of posting about using the R1C1 format to select cells when
using VB to program a function into a cell, but not a lot about how to use
the R1C1 format to select a range for use in a VB Macro.

If I am in cell CA91 and I want to select the range starting at CA92 through
CE176, how to I program that into VB? I tried "range(rc1:r4c84).select" and
it doesn't like the colon. I then tried "range(rc[1]:r[4]c[84]).select" and
it bombed on the first open bracket ([).

Any ideas? I can't use the direct addresses since I won't always know where
I'm going to start this macro from.

if you want Range("R1C1:R4C84") to be selected, you can use the following
code:
1. Range("A1:CE4").select
or
2. Range(Cells(1, 1), Cells(4, 84)).select

Best Regards
John Black

"Robert_L_Ross" <[email protected]> wrote in message
news:[email protected]...
> I've seen a lot of posting about using the R1C1 format to select cells
when
> using VB to program a function into a cell, but not a lot about how to use
> the R1C1 format to select a range for use in a VB Macro.
>
> If I am in cell CA91 and I want to select the range starting at CA92
through
> CE176, how to I program that into VB? I tried "range(rc1:r4c84).select"
and
> it doesn't like the colon. I then tried "range(rc[1]:r[4]c[84]).select"
and
> it bombed on the first open bracket ([).
>
> Any ideas? I can't use the direct addresses since I won't always know
where
> I'm going to start this macro from.

Robert,
You might find this easier:

Sub GetRange()
Dim rng As Range
Set rng = Range("CA92").Resize(85, 5)
rng.Select
End Sub

Or .... where Activecell=CA91 to use your example

Sub GetRange()
Dim rng As Range
Set rng = ActiveCell.Offset(1, 0).Resize(85, 5)
rng.Select
End Sub

Avoid using select as much as possible as it slows performance. I used it to
illustrate that the range was correct.

HTH

"Robert_L_Ross" wrote:

> I've seen a lot of posting about using the R1C1 format to select cells when
> using VB to program a function into a cell, but not a lot about how to use
> the R1C1 format to select a range for use in a VB Macro.
>
> If I am in cell CA91 and I want to select the range starting at CA92 through
> CE176, how to I program that into VB? I tried "range(rc1:r4c84).select" and
> it doesn't like the colon. I then tried "range(rc[1]:r[4]c[84]).select" and
> it bombed on the first open bracket ([).
>
> Any ideas? I can't use the direct addresses since I won't always know where
> I'm going to start this macro from.

hi

I want to change the below code into cell ranges instead of r1c1. Also i want this formula for multiple cells

Sub Item()
'
' Item Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Sheet5!$A$8:$A$156"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("D2:O2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R2C16,Sheet5!R[6]C[-3]:R[150]C[15],Sheet5!R[4]C[-1],0)"
Range("D3:O3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R2C16,Sheet5!R[5]C[-3]:R[147]C[15],Sheet5!R[3]C,0)"
Range("D4:O4").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R2C16,Sheet5!R[4]C[-3]:R[146]C[15],Sheet5!R[2]C[1],0)"
Range("D5:O5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R2C16,Sheet5!R[3]C[-3]:R[145]C[15],Sheet5!R[1]C[2],0)"
Range("D6:F6").Select


End Sub


Pls. kindly help on this