Excel 2007 VBA - filling an array element with results from a vlookup

[Kramxel]

Hi all,

I'm working with arrays (as a learning tool so far they're awesome) but i'm having a couple of problems which I'm struggling to solve.

assuming my array is defined as Arr = (N, 34) where N is a fluctuating number of rows how would I populate (N, 34 ) with the results of the vlookup where my lookup reference is (N, 2) and my lookup array is a range("A:B") on a worksheet("Sheet1").

I tried:

Please Login or Register  to view this content.

But this through up a runtime error 1004 unable to get the VLookup property of the worksheetfunction class?

my second issue which i think isn't possible but I thought I'd ask anyhow is is it possible to perform "COUNTIFS" within the array (and potentially write this result to an element within a second array) rather than what i'm doing at the moment which is the array back to a worksheet and doing the calculation there rather than within the array.

[Kaper]

Are you sure that value stored in Arr(N,2) is present in column A Sheet1?
Probably this is the cause.
Just to make sure, I tested the short code:

Please Login or Register  to view this content.

and if there is 100 in column A code works perfect, while it there is no such value - error 1004 occurs.

[Kaper]

PS. if that's the case think of some error handling. Simplest one could be for instance:

Please Login or Register  to view this content.

As far as I know, you cant use COUNTIFS for an array. So either you can do what you did before (pasting array temporarily to the worksheet) or just use a loop to go through array rows counting these wher conditions you would use in COUNTIFS are fulfilled)

[Kramxel]

Hi Kaper,

You're spot on my look up reference in some instances isn't in the range A:B (this is actually intentional, the Range A:B is created by the values in the data that feeds (N, 2) but I have then got rid of entries i didn't want to look at, forgetting that the'd still at this point be in the data going into the array.

just deciding if I should be ignoring the error and moving on or if i should remove these entries from the raw data before loading it into the array.