Collection of Ranges in VBA

Question

my issue is the following: I have an Excel front end for a Oracle database with material data in it. I have an SQL query which brings up the x-y Data in the database. for example this might be a stress-strain curve.

I want to display this data as individual curves. Since the x and y data of all tests are now in two columns, one for x and one for y, I need to split them. I do not want to open a new worksheet for this so I decided to make two collection of ranges. One collection will hold all x ranges and one will hold all y ranges.

So the different curves are below each other. I have a code which splits them and makes the ranges. I am 95% sure the code for splitting works, but the problem is that the items of the collection can now not be accessed as intended from me. Here the code so you can make yourself a picture of what is going on

    Sub Split()
Dim X As New Collection, Y As New Collection
Dim tbl As ListObject
Dim cell As Range, rng As Range, xrng As Range, yrng As Range
Dim U As Range, L As Range ' upper and lower boundary of each x and y range
Dim i As Integer

Set tbl = Data.ListObjects(1)

Set cell = tbl.ListColumns(35).DataBodyRange(1, 1)

Do While Not (IsEmpty(cell))
      Set U = cell
      Set L = cell
      Do While (L.Value < L.Offset(1, 0).Value) And (Not (IsEmpty(L.Offset(1, 0))))
            Set L = L.Offset(1, 0)
      Loop
      Set xrng = Range(U, L)
      Set yrng = Range(U.Offset(0, 1), L.Offset(0, 1))
      X.Add (xrng)
      Y.Add (yrng)
      
      ' move one down
      Set U = L.Offset(1, 0)
      Set cell = U
Loop



Debug.Print xrng.Select ' returns TRUE
Debug.Print X.Item(X.Count).Select ' Error Object needed

End Sub

A short description of the code is: Go through a listobject with the x and y data. Define a U(pper) and L(ower) boundary cell for each x range and y range. If X drops in value this is a x from the next range because x is always ascending ordered in the x y data. Then save the ranges and move the U(pper) boundary and the loop cell down to the next range of x and y.

X.item(1)

does not return a type range but rather variant. How can I make it a range object. Or in other word is there a way to save range objects in a other object with not pre defined length (rules out array)?

Answer 1

Solved it: there was an issue with collection.add Apprenetly if you type:

  X.Add (xrng)
  Y.Add (yrng)

X(1) will have the Type variant

if you type:

  X.Add xrng
  Y.Add yrng

The Type will be range. No clue why..