Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find each of the items in an array and save result in another array | Excel Programming | |||
waiting Query result | Excel Programming | |||
Replace ^ in web query result | Excel Worksheet Functions | |||
Replace ^ in web query result | Excel Programming | |||
Importing result from Access query to Excel but the result only c. | Excel Discussion (Misc queries) |