Describing a Range with Variables

[mazo]

Hi

I have the following problem:
I want to select a certain Range depending on the value of a variable

e.g.
I habe a variable called nr (current value = 1)
I want to replace:

Range("O1:O200").Select

with:

" Range [ Cells(1,(14+nr)) to Cells(200,(14+nr)) ]"

How can you wright that in VBA?

Mazo

[Norman Jones]

Hi Mazo,

You are almost there! Try:

Dim rng As Range

Set rng = Range(Cells(1, 14 + i), Cells(200, 14 + i))


---
Regards,
Norman



"mazo" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I have the following problem:
> I want to select a certain Range depending on the value of a variable
>
> e.g.
> I habe a variable called nr (current value = 1)
> I want to replace:
>
> Range("O1:O200").Select
>
> with:
>
> " Range [ Cells(1,(14+nr)) to Cells(200,(14+nr)) ]"
>
> How can you wright that in VBA?
>
> Mazo
>

[Dave Peterson]

Another way:

Dim myRng as range
dim nr as long
nr = 1 'or something

set myrng = activesheet.cells(1,14+nr).resize(1,200)

..resize(1,200) means to take the original range (cells(1,14+nr) and resize it to
1 row by 200 columns.





mazo wrote:
>
> Hi
>
> I have the following problem:
> I want to select a certain Range depending on the value of a variable
>
> e.g.
> I habe a variable called nr (current value = 1)
> I want to replace:
>
> Range("O1:O200").Select
>
> with:
>
> " Range [ Cells(1,(14+nr)) to Cells(200,(14+nr)) ]"
>
> How can you wright that in VBA?
>
> Mazo

--

Dave Peterson