Referencing variable Range in a loop

Dear all,
I posted this problem yesterday, but I could not find my posting in the
discussion group. I would like to repeat it. I am trying to referece variable
range in a loop in different worksheets. I tried, using Range(Cells(...),
Cells(...)), it works when I am working with only one worksheet and when the
worksheet is activated. However, I want it to work without activating the
worksheet and when I am working with multiple worksheets and workbooks too.
The second try, I did was by trying to create a text using concatenate to
obtain Range, but I could not succeed.
I would be glad if someone will answer both of my problems.
Thanks in Advance.
Samirkc

HI,
I got the answer, one should using concatenate (&) as

WorkSheets("VarNoInterpolation").Range("a2:i" & nr_agegroup + 1).Value = 12

when will reference a range a2:i29 (when nr_agegroup = 28).

I am still waiting for any other suggestions using Range and Cells.
Samir



"Samirkc" wrote:

> Dear all,
> I posted this problem yesterday, but I could not find my posting in the
> discussion group. I would like to repeat it. I am trying to referece variable
> range in a loop in different worksheets. I tried, using Range(Cells(...),
> Cells(...)), it works when I am working with only one worksheet and when the
> worksheet is activated. However, I want it to work without activating the
> worksheet and when I am working with multiple worksheets and workbooks too.
> The second try, I did was by trying to create a text using concatenate to
> obtain Range, but I could not succeed.
> I would be glad if someone will answer both of my problems.
> Thanks in Advance.
> Samirkc
>

Looking back at your previous post, you can do this type of syntax to convert
cell addresses to ranges (it's kind of a round-about way of doing it but it
works).

Dim rng1 As String
Dim rng2 As String
rng1 = Range(Cells(2, 1), Cells(number_rows + 1, 9)).Address(False, False)
rng2 = Range(Cells(2, 1), Cells(number_rows + 1 + d, 9)).Address(False, False)
Sheets(1).Range(rng1).Value = Sheets(2).Range(rng2).Value

(Keep in mind that if destination range is larger than source range you'll
get "#N/A" in some cells). Doing it this way below causes an error.
Something about "Cells" object not being active.

Sheets(1).Range(Cells(2, 1), Cells(number_rows + 1, 9)).Value = _
Sheets(2).Range(Cells(2, 1), Cells(number_rows + 1 + d, 9)).Value

you would have to use the "With -- End With" construct and use ".Cells"

With Sheets(1)
..Range(.Cells(2, 1), .Cells(number_rows + 1, 9)).Value = _
..Range(.Cells(2, 1), .Cells(number_rows + 1 + d, 9)).Value
End With

But that syntax only allows for copying within one sheet. That's about the
extent of my knowledge. HTH.


"Samirkc" wrote:

> HI,
> I got the answer, one should using concatenate (&) as
>
> WorkSheets("VarNoInterpolation").Range("a2:i" & nr_agegroup + 1).Value = 12
>
> when will reference a range a2:i29 (when nr_agegroup = 28).
>
> I am still waiting for any other suggestions using Range and Cells.
> Samir
>
>
>
> "Samirkc" wrote:
>
> > Dear all,
> > I posted this problem yesterday, but I could not find my posting in the
> > discussion group. I would like to repeat it. I am trying to referece variable
> > range in a loop in different worksheets. I tried, using Range(Cells(...),
> > Cells(...)), it works when I am working with only one worksheet and when the
> > worksheet is activated. However, I want it to work without activating the
> > worksheet and when I am working with multiple worksheets and workbooks too.
> > The second try, I did was by trying to create a text using concatenate to
> > obtain Range, but I could not succeed.
> > I would be glad if someone will answer both of my problems.
> > Thanks in Advance.
> > Samirkc
> >