Hi, I have this following piece of code which assignes a range to an array, take a look:
Dim rngcells1 as range
ReDim arrcells1(0 To i, 0 To rngcells1.Rows.Count - 1)
For k = 1 To i
For cellx = 1 To rngcells1.Rows.Count - 1
arrcells1(k - 1, cellx - 1) = Cells(rngcells1(cellx + 1, 2), rngcells1(cellx + 1, 3)).Offset(0, tl8 * (k - 1)).Value
Debug.Print arrcells1(k - 1, cellx - 1)
Next cellx
Next k
But now, as I have 12 dynamic ranges and not just 1, I have defined the ranges like this:
Dim rngcells(1 To 12) As Range
Dim rngGroup As Range
'just defines the dynamic range
Set rngGroup = [E9].Offset(, (5 * (i - 1)))
Set rngcells(i) = Range(Range(rngGroup, rngGroup.End(xlToRight)), Range(rngGroup, rngGroup.End(xlToRight)).End(xlDown))
If rngcells(i).Rows.Count< 42 Then
For k = 2 To rngcells(i).Rows.Count
Debug.Print rngcells(i)(k, 1)
Next k
End If
So, back in my initial code, I just have to replace rngCells1 by rngCells(p) say and put all the code in a loop from p = 1 to 12 for it to work for the 12 dynamic ranges.
But, how do I adjust the array assignment now, i.e. the line
arrcells1(k - 1, cellx - 1) = Cells(rngcells1(cellx + 1, 2), rngcells1(cellx + 1, 3)).Offset(0, tl8 * (k - 1)).Value
Replacing rngcells1 with rngcells(p) works ok. However, I am not sure what to change arrcells1 to. I have tried changing it to arrcells(p) likewise but it doesn't seem to work. Have I defined the array wrongly or something? I would have thought that
arrayCells(p)(k - 1, cellx - 1) = Cells(rngcells(p)(cellx + 1, 2), rngcells(p)(cellx + 1, 3)).Offset(0, tl8 * (k - 1)).Value
would work, but it doesn't for me!
Also, just for completeness, I had this code just after the above, for just the one range case:
[B65336].End(xlUp).Offset(1, 0).Select
Range(Selection, Selection.Offset(i - 1, rngcells1.Rows.Count - 2)) = arrcells1
So, whatever this new array becomes, it must be able to be put onto a worksheet like that.
I hope I have not confused everyone too much.
Can anyone help me?? I would be most grateful!
Thanks,
RET79
This message was edited by RET79 on 2002-04-03 09:06
Dim rngcells1 as range
ReDim arrcells1(0 To i, 0 To rngcells1.Rows.Count - 1)
For k = 1 To i
For cellx = 1 To rngcells1.Rows.Count - 1
arrcells1(k - 1, cellx - 1) = Cells(rngcells1(cellx + 1, 2), rngcells1(cellx + 1, 3)).Offset(0, tl8 * (k - 1)).Value
Debug.Print arrcells1(k - 1, cellx - 1)
Next cellx
Next k
But now, as I have 12 dynamic ranges and not just 1, I have defined the ranges like this:
Dim rngcells(1 To 12) As Range
Dim rngGroup As Range
'just defines the dynamic range
Set rngGroup = [E9].Offset(, (5 * (i - 1)))
Set rngcells(i) = Range(Range(rngGroup, rngGroup.End(xlToRight)), Range(rngGroup, rngGroup.End(xlToRight)).End(xlDown))
If rngcells(i).Rows.Count< 42 Then
For k = 2 To rngcells(i).Rows.Count
Debug.Print rngcells(i)(k, 1)
Next k
End If
So, back in my initial code, I just have to replace rngCells1 by rngCells(p) say and put all the code in a loop from p = 1 to 12 for it to work for the 12 dynamic ranges.
But, how do I adjust the array assignment now, i.e. the line
arrcells1(k - 1, cellx - 1) = Cells(rngcells1(cellx + 1, 2), rngcells1(cellx + 1, 3)).Offset(0, tl8 * (k - 1)).Value
Replacing rngcells1 with rngcells(p) works ok. However, I am not sure what to change arrcells1 to. I have tried changing it to arrcells(p) likewise but it doesn't seem to work. Have I defined the array wrongly or something? I would have thought that
arrayCells(p)(k - 1, cellx - 1) = Cells(rngcells(p)(cellx + 1, 2), rngcells(p)(cellx + 1, 3)).Offset(0, tl8 * (k - 1)).Value
would work, but it doesn't for me!
Also, just for completeness, I had this code just after the above, for just the one range case:
[B65336].End(xlUp).Offset(1, 0).Select
Range(Selection, Selection.Offset(i - 1, rngcells1.Rows.Count - 2)) = arrcells1
So, whatever this new array becomes, it must be able to be put onto a worksheet like that.
I hope I have not confused everyone too much.
Can anyone help me?? I would be most grateful!
Thanks,
RET79
This message was edited by RET79 on 2002-04-03 09:06
