check if value is in array

I am using a function I found online called "IsInArray," which checks if a value is in an array and returns true of false. It doesn't seem to be working in this case. In the example below, I have a named range "names", and I have a sub which stores the range in an array and runs IsInArray for "BLUE WATER JEWELERS" in the array. This returns false even though the value is in the range.

The workbook with the code: ExistsInArray.xlsm

Here is the named range called "names":

Account Name
ALBERT S SMYTH CO, INC
BILL FRENCH JEWELERS
BLAND'S JEWELERS
BLUE WATER JEWELERS
BRAUNSCHWEIGER JEWELERS

here is the VBA:

Please Login or Register  to view this content.

I think that your test passes a two dimensional array to the function, albeit, the second dimension is 1. Using Transpose on a vertical range converts it to a one dimensional array ... which is what, I think, the function is expecting. Proof ... pudding

Please Login or Register  to view this content.

Regards, TMS

Loading a range from a worksheet to an array creates a 2 dimensional array - you're aware of this as you use a Message Box to show element (5,1)

The JOIN() function cannot be used to join a multi-dimensional array...

Your code modified to use a function by a 3rd party (http://excellerando.blogspot.co.uk/2...ons-for-2.html)

Please Login or Register  to view this content.

Quickly tested and seems to work - but you should stress it a little...

(Offered as an example - I had other uses for the code recently)

A shorter version using COUNTIF without the complication of the dimensions of an array. The data can be column based, row based, or 2D range based.

Please Login or Register  to view this content.

You could probably easily adapt this to take a third parameter to determine if an exact match was required.


Regards, TMS