query result through array on worksheet
 

I have a query in Access which results I put into a two dimensional array in
Excel through ADO.

The array has the columns month & count.
What I would like to do is place the results in a worksheet neatly putting
the count of month 5 in the cell for month 5.

Only problem is, when there is no count in month 5, it is not shown in the
query.
So if the query results a

Month: Count
1: 50
2: 29
3: 11
4: 40
6: 30

It misses month 5. On the worksheet, month 5 should be displayed as 0.

I've tried Vlookup but that didn't work.
How do I do this?

query result through array on worksheet
 

Just transfer the values to a second fixed array with 2 column and 12 rows.
Something like this:

Dim i As Long
Dim arr2(1 To 12, 1 To 2)

'prepare the final array
For i = 1 To 12
arr2(i, 1) = i
arr2(i, 2) = 0
Next

'add the values from the first array
For i = 1 To UBound(arr1)
arr2(arr1(i, 1), 2) = arr1(i, 2)
Next

Where arr1 is the one you showed in this post.

RBS

"Ozz" wrote in message
...
I have a query in Access which results I put into a two dimensional array
in
Excel through ADO.

The array has the columns month & count.
What I would like to do is place the results in a worksheet neatly putting
the count of month 5 in the cell for month 5.

Only problem is, when there is no count in month 5, it is not shown in the
query.
So if the query results a

Month: Count
1: 50
2: 29
3: 11
4: 40
6: 30

It misses month 5. On the worksheet, month 5 should be displayed as 0.

I've tried Vlookup but that didn't work.
How do I do this?