Best way to compare values in a range?

[jp001]

Using VBA, I want to import data from a text file into a worksheet, but only import a row of text if the three-letter code matches one I have in a named range. The data looks similar to this:

ALF,D,06/01/2009,0.96,0.97,0.96,0.97,1007,0
ALK,D,06/01/2009,0.34,0.36,0.34,0.345,4375,0
ALL,D,06/01/2009,3.7,3.8,3.68,3.75,12381,0
ALR,D,06/01/2009,1.155,1.18,1.155,1.17,388,0
ALS,D,06/01/2009,3.39,3.39,3.23,3.31,2642,0
ALT,D,06/01/2009,0.031,0.031,0.031,0.031,3460,0
ALY,D,06/01/2009,0.1,0.105,0.1,0.105,460,0
etc...

and the named range similar to this:

AAC
AAF
AAM
AAR
AAX
ABB
ABC
ABP
ABQ
etc....

I know how to open and import the data, it's just making the comparisons
that I'm having trouble with at the moment . Any ideas, maybe an example of VBA code which does something similar please?

[Leith Ross]

Hello jp001,

The easiest and quickest method is to use an array. The drawback with a standard array is you can't easily check if an entry has already been made. You can get the best of both using a Dictionary Object. The name is really a misnomer. It is an associative array which has the capability of checking if a member already exists. Have you used this before?

[jp001]

Thanks Leith, no I've haven't used that before. Would you be able
to point me towards a code sample? Thanks!

[Leith Ross]

Hello jp001,

Here is a sample workbook using the Dictionary Object to compare the range names. I have a list of range names in column "M" on "Sheet1". The data is read in from a text file using the data you posted. The first field of the line data is tested using the DSO to see if the rnage name exists. If it does then it will copy the input fields to A1:H1 and go down one row for each match.

Please Login or Register  to view this content.

[jp001]

Thanks for that Leith! (Been away for a few days and just saw your answer.) Actually I found a similar one with the search, but it's useful to see different ways of doing the same thing. Much appreciated!