Search code examples
excelvba

Copying table to collection in VBA

I have a sheet with a table that has 2 columns. I want to pair each cell in column "A" with the cell immediately adjacent in column "B" as data/key in a collection.

A1,B1 A2,B2 A3,B3

and so forth.

For Each ws In wb_test.Worksheets
        For Each Cell In ws.UsedRange.Cells
            c.Add
        Next
    Next

I am not sure how to iterate through just column "A" and grab the data in column "B".

Solution

  • From your description of the issue, it sounds like you need to read each cell of the first column of the used range, and then pick the cell adjacent to it using Offset:

    For Each ws In wb_test.Worksheets
    For Each cell In ws.UsedRange.Columns(1).Cells
        MyCollection.Add cell.Value, cell.Offset(, 1).Value
    Next
Next

    That said, unless your worksheets are very rigidly built, you might want to find a better way of detecting your data as obviously you don't want to hit empty cells etc.