VBA, VLOOKUP possible in EVALUATE function? Results not as expected

[JackDanIce]

Hi,

I have a two column VLOOKUP table, which currently I'm passing to a dictionary object to replace output with mapped data.

I'm trying to see if I can reduce the amount of code by using VLOOKUP within EVALUATE, so far have tried:

Sub test()

'F5:F8
With Cells(5, 6).Resize(4)
    .Offset(,1).Value = Evaluate(Replace("IF((@<>""""),VLOOKUP(@,Error_Values,2,0),"""")", "@", .Address))
End With


End Sub

F5:F8 contains either valid keys (A, B, C) from the VLOOKUP table or empty cells

If F5 contains A, then relative for all non empty cells in F6:F8, G5:G8 contains the mapped item to A only
If F5 contains A and F6:F8 contains blank and non valid keys, then G5:G8, if not blank, contains mapped item to A only
If F5 is blank then #N/A returns for relative in G6:G8 (The VLOOKUP table doesn't allow for a blank key)

It seems to only return the vlookup value for F5, if it's not an error for all cells in G6:G8 where F6:F8 is not blank, regardless of what F6:F8 has

Can VLOOKUP be used in EVALUATE like this, if so how?

TIA,
Jack

 

[JackDanIce]

Bump

 

[RoryA]

VLOOKUP is extremely difficult, if not impossible, to make work for this. Is your table sorted? If so, LOOKUP can return an array pretty easily.

 

[JackDanIce]

Hi RoryA,

To be honest, it's not an absolute need, but to improve my understanding of how to use Evaluate in VBA (see a lot of MVP's use it, shortens code, something new).

I already have a dictionary of the vlookup table and when I iterate over a table of values in an array, one of the columns, I'm applying this dictionary to and replacing contents if value exists.

I thought i could skip this part of the loop, then when the table is printed, I could run a single Evaluate command over it.

The keys are ordered (Err1, Err2, Err3... Err11), their items are not (Err1 - B, Err2 - D, Err3 - A.... Err11 - H)

 

[steve the fish]

Does this do it?

Set Rng = Cells(5, 7).Resize(4)
Rng.Value = WorksheetFunction.IfError(WorksheetFunction.VLookup(Rng.Offset(, -1), Range("Error_Values"), 2, 0), "")

 

[XOR LX]

Hi,

To coerce the array of returns from VLOOKUP would require e.g.:

With Cells(5, 6).Resize(4)
    .Offset(, 1).Value = Evaluate(Replace("IF((@<>""""),VLOOKUP(T(IF({1},@)),Error_Values,2,0),"""")", "@", .Address))
End With

Regards

 

[RoryA]

shortens code

That's not always a good thing. Clear code beats short code any day of the week.

 

[JackDanIce]

@steve the fish, thank you, although was trying to avoid the worksheet.function approach. This is to practise using EVALUATE within VBA otherwise stick to dictionary
@XOR LX, T(IF({1},@)) is this part creating the input array of values to pass as look up value in VLOOKUP, where mine wasn't? It works how I was hoping, thank you.
@RoryA lol are you suggesting if I'm run over by a bus tomorrow clear code is better than complex code? No this is job protection!

It's going to be sent to clients to return data, but client to run macro before returning so they can correct any of their data input errors to reduce amount of back and forth communications. Likely be asked to password lock the code and I've written code documentation (without this change)

Thank you all though, much appreciated
Jack

 

[RoryA]

Why do you need job protection if you're run over by a bus??

 

[JackDanIce]

Life policy insurance scam, shhhh!! Plus unlike Superman, I wear my pants on the INside (and can't stop a moving bus evidently....)