Refering to columns in a loop

Question

I am having a trouble to put data of a collection in a resized set of columns. here is the part of code that worked fine for one type of collections:

For i = 1 To lim
    Set itm = col(i)
    r(i, 1) = itm(1)
    s = itm(2)
    r(i, 2) = s
    For j = 3 To itm.count
      s = s & "_" & itm(j)
      
    Next
    r(i, 3) = s
    r(i, 4) = itm.count - 1
  Next
  Statistics_CellIDs.[A2].Resize(lim, 4) = r
  GoTo NextTreatment
ColNoItm:
  Set itm = New Collection
  col.Add itm, s
  itm.Add s
  Resume ColNoItmCont

so now I want to loop for different types of collections and put the results in every 8 columns for each type (first 8 columns contains type 1 collection; second 8 columns which are from column 9 to 16 contains 2nd type of collection...etc)

so I changed the line where I put the data to:

Statistics_CellIDs.Columns([2], count + 8 * (count - 1) + 4).Resize(lim, 4) = r

instead of:

Statistics_CellIDs.[A2].Resize(lim, 4) = r

I got the error "application-defined or object-defined error". any help please?

Answer 1

To get the correct address use

Statistics_CellIDs.Cells(2, 1 + count * 8).Resize(lim, 4) = r

Change 1 to a number you need to get the correct position.