Thread: Setting a Range depending on variables

Hi

Can somebody help me?
I have two expressions that seem similar to me. But one isn't working.


("rowstart" and "nrmess" are both integer variables with positive
values;
"rng" and "rng2" are both "dim as range")

This works:

Set rng = Measurement.Range(Cells(4070 + rowstart, 4), Cells(4120 +
rowstart, 4))

This doesn't work:

Set rng2 = Sheets("data").Range(Cells(410, nrmess * 3 + 12), Cells(415,
nrmess * 3 + 12))

Can somebody tell me, what is the reason??

Thank you

You should use

With Sheets("data")
.Range(.Cells(410, nrmess * 3 + 12), .Cells(415,nrmess * 3 + 12))
End With

but it may be that nrmess * 3 + 12 is greater than 255


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mazo" <m.zeiler@gmx.de> wrote in message
news:1147794719.533773.72120@j73g2000cwa.googlegroups.com...
> Hi
>
> Can somebody help me?
> I have two expressions that seem similar to me. But one isn't working.
>
>
> ("rowstart" and "nrmess" are both integer variables with positive
> values;
> "rng" and "rng2" are both "dim as range")
>
> This works:
>
> Set rng = Measurement.Range(Cells(4070 + rowstart, 4), Cells(4120 +
> rowstart, 4))
>
> This doesn't work:
>
> Set rng2 = Sheets("data").Range(Cells(410, nrmess * 3 + 12), Cells(415,
> nrmess * 3 + 12))
>
> Can somebody tell me, what is the reason??
>
> Thank you
>

Both are subject to failure. The one that works is because you are working
on the activesheet. When working off the activesheet, all references need to
be qualified

With Worksheets("Data")
Set rng2 = .Range(.Cells(410, nrmess * 3 + 12), _
.Cells(415, nrmess * 3 + 12))
End With

Unqualifed range references refer to the activesheet unless used in a sheet
module. then they refer to the sheet containing the code.

--
Regards,
Tom Ogilvy


"mazo" wrote:

> Hi
>
> Can somebody help me?
> I have two expressions that seem similar to me. But one isn't working.
>
>
> ("rowstart" and "nrmess" are both integer variables with positive
> values;
> "rng" and "rng2" are both "dim as range")
>
> This works:
>
> Set rng = Measurement.Range(Cells(4070 + rowstart, 4), Cells(4120 +
> rowstart, 4))
>
> This doesn't work:
>
> Set rng2 = Sheets("data").Range(Cells(410, nrmess * 3 + 12), Cells(415,
> nrmess * 3 + 12))
>
> Can somebody tell me, what is the reason??
>
> Thank you
>
>