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