VBA create an array and use it in a Vlookup

[anglais428]

Hi,

I am looking to define a list in VBA and then use that list as the range in a vlookup formula.

Something like the below:

MyRange = Array("AA","AB")

Result = Application.VLookup(Left(A2, 2), MyRange, 1, False)

The ideal results would be if whatever the first two digits of the text in cell A2 is contained in MyRange then it will return those two digits - else it with provide a #N/A error. E.g. if AA001 is in cell A2, the result would be "AA"; if it was "AC" then the result would be "#N/A".
Any ideas?

 

[Fluff]

How about

myRange = Array("AA", "AB")

result = Application.VLookup(Left(Range("A2"), 2), myRange, 1, 0)
Range("A3") = result

 

[anglais428]

Thanks for the reply. This is given a type mismatch error.

 

[Fluff]

On which line do you get that error?
Also what is the value in A2?

 

[anglais428]

It was on the "results" line.
The values in A2 can be a mixture of numbers and text.
I want the result to be an error (#N/A) if the two letters are not found from the lookup, else return the two letters.

Thanks for your help

 

[Fluff]

Can you supply the whole code?

 

[anglais428]

I've changed to the below and it does give an #n/a which is what I was expecting. However, how would I modify this to keep the formula (currently it is the value rather than the formula in the cell)?

myRange = Array("CS", "GN", "GS", "NA", "NC", "NF", "NI", "NL", "NO", "NP", "NR", "NZ", "R0", "SA", "SC", "SF", "SG", "SI", "SL", "SO", "SP", "SR", "SZ")


Range("C2").Select

ActiveCell.Formula = Application.VLookup(Left(Range("B2"), 2), myRange, 1, 0)

 

[Fluff]

Try

ActiveCell.Formula = "=VLookup(Left(b2, 2),{""" & Join(myRange, """;""") & """}, 1, 0)"

 

[anglais428]

Fantastic - works perfectly. I would never have thought of the Join function.

Many thanks.

 

[Fluff]

You're welcome & thanks for the feedback