VBA Alternative Match

[Julmust Jaeger]

Hello,

This might be more conceptual question, but example code is appreciated, but I have a formula in a cell (AH2) that I use and then autofill the rest of the column with.

=NOT(ISERROR(MATCH(L2,Canceled!A:A,0)))

How would one do something similar using VBA?

Really, I all I conceptually would like to do is in some more efficient VBA way just check the values in L2 (a user number) and check if that is found in the other worksheet (called Canceled).

There are 361,216 rows (and 26 columns), so any efficient solutions are especially appreciated (I'm finding the formula quite slow and I am trying to get a better grasp of how to use VBA).

Cheers!

 

[Dan_W]

In VBA, you could put the values in each sheet into separate arrays, and then undertake the comparison in memory - that would conceivably be alot quicker. That's one option. I'm not at my computer at the moment to give an example, but can do so later later.

 

[Dan_W]

Sorry, I just reread your post. Are you only checking one number against the list in the Cancelled sheet?

 

[Julmust Jaeger]

For each row there is only one number (invoice number), basically this is step 1 in running an autofilter to remove all the canceled invoices (via this new column as some rows are the same invoice prior to the cancelation and I need to "catch" all of these).

There are many rows though that reflect an invoice number that has been canceled.

Good question though!