Excel VBA construct nested for loops to reference tables

Question

I have a two pivot tables constructed from some data (without the same structure), and I need to compare each entry using the Cartesian product of some other tables.

I'd like to have some nested for loops in VBA which grab data from the pivot tables based on some lists I've constructed.

My Lists:

+------+-----+
| Date | ID  |
+------+-----+
| 9/1  | 123 |
| 9/2  | 124 |
| 9/3  | 200 |
| 9/4  | 201 |
|      | 202 |
|      | 300 |
|      | 500 |
|      | 999 |
+------+-----+

I can't figure out how to reference each entry in each column in these lists to use them in a command. I have in mind something like the following:

for each day in Date:
  for each num in ID:
    do_pivot_fetch(from pivot1, date=day, ID=num)

Kirk Schwenkler · Accepted Answer · 2014-09-16 15:53:03Z

If these lists are maintained in Excel (this is how I always write these things, that way it's easy to change parameters when you need to), I usually use do...while to iterate over them. You can also try to find the last filled cell in each row and use it to calculate the range of a for loop, but I've found that to be glitchy given the things people like to do with excel spreadsheets.

So let's say you have the lists in column A (Date) and B (ID) on sheet 'Params'. Assuming you have headers in row 1, and an empty row after the last value in each list. Then your code would look something like this:

Sub useParams()
Dim Date_val, ID_val As Range
With ThisWorkbook.Worksheets("Params")
    Set Date_val = Range("A2")
    Do While Date_val.Value <> ""
        Set ID_val = Range("B2")
            Do While ID_val.Value <> ""
               //do_pivot_fetch(from pivot1, date=Date_val.Value, ID=ID_val.Value)
               Set ID_val = ID_val.Offset(1, 0)
            Loop
        Set Dateval = Date_val.Offset(1, 0)
   Loop
End With
End Sub

Collectives™ on Stack Overflow

Excel VBA construct nested for loops to reference tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related