# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  > [SOLVED] Using R1C1 in VB to select a range

## Robert_L_Ross

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.

----------


## john

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" <RobertLRoss@discussions.microsoft.com> wrote in message
news:726D6EAC-EAE4-44C5-9650-14562EA91F09@microsoft.com...
> 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.

----------


## Toppers

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.

----------


## vshah

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

----------


## AliGW

*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------

