VBA Evaluate Range and VLOOKUP

[DocAElstein]

Hi,
As a beginner I may be going a bit above my head!!

After studying and participating at some considerable length in Threads and links to do with using the Evaluate function to speed things up, I thought I understood it. Here are some of those links and Threads. (www.excelfox.com/forum/f22/concatenating-balls-1891/ VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan )

. So … the following simplified example File (XL2007 .xlsm)
https://app.box.com/s/pr78mhna00advvhsrmvi
has a Spreadsheet LEFT Function and a Spreadsheet VLOOKUP Function

The results look good! (That is to say wot I expect!) :-

Book1
	A	B	C	D
1	Produnt
2	Name
3	Chocolate-europe aroma	4		Choc
4	Chocolate-Cookies	0		Choc
5	Banana-Chocolate-Split	10		Bana
6	Limette-Ksekuchen	16		Lime
7	Erdbeere-Quark	8		Erdb
8	Erdbeere-Mix	0		Erdb
9	Jamaica Sun	6		Jama
10	Waldbeeren	0		Wald
11
12
13
14	LOOKUP Table
15	Product Name
16	Haselnu-Walnu-aromatisiert
17	Tiramisu		2
18	Chocolate-colonial blend
19	Chocolate-europe aroma		4
20	Chocolate-Cookies
21	Jamaica Sun		6
22	Himbeere-Joghurt
23	Erdbeere-Quark		8
24	Erdbeere-Mix
25	Banana-Chocolate-Split		10
26	Waldbeeren
27	Kirsche		12
28	Kirsche-grner Apfel
29	Kirsche-Ananas		14
30	Stracciatella
31	Limette-Ksekuchen		16
32	grner Apfel-Quark
33	Blutorange-Quark
Sheet1

Cell Formulas
Range	Formula
B3	=VLOOKUP(A3,$A$16:$C$33,3,FALSE)
B4	=VLOOKUP(A4,$A$16:$C$33,3,FALSE)
B5	=VLOOKUP(A5,$A$16:$C$33,3,FALSE)
B6	=VLOOKUP(A6,$A$16:$C$33,3,FALSE)
B7	=VLOOKUP(A7,$A$16:$C$33,3,FALSE)
B8	=VLOOKUP(A8,$A$16:$C$33,3,FALSE)
B9	=VLOOKUP(A9,$A$16:$C$33,3,FALSE)
B10	=VLOOKUP(A10,$A$16:$C$33,3,FALSE)
D3	=LEFT(A3,4)
D4	=LEFT(A4,4)
D5	=LEFT(A5,4)
D6	=LEFT(A6,4)
D7	=LEFT(A7,4)
D8	=LEFT(A8,4)
D9	=LEFT(A9,4)
D10	=LEFT(A10,4)

I apply this code

[color=darkblue]Sub[/color] Evaluate_Left()[color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
[color=darkblue]Dim[/color] rngEE [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngEE = ThisWorkbook.Worksheets("sheet1").Range("E3:E10")
[color=darkblue]Let[/color] rngEE = Evaluate("if(row(3:10),LEFT(" & rngName.Address & ",4))")
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_Left()[/color]

And get the following:-

Book1
	A	B	C	D	E
1	Produnt
2	Name
3	Chocolate-europe aroma	4		Choc	Choc
4	Chocolate-Cookies	0		Choc	Choc
5	Banana-Chocolate-Split	10		Bana	Bana
6	Limette-Ksekuchen	16		Lime	Lime
7	Erdbeere-Quark	8		Erdb	Erdb
8	Erdbeere-Mix	0		Erdb	Erdb
9	Jamaica Sun	6		Jama	Jama
10	Waldbeeren	0		Wald	Wald
11
12
Sheet1

Which again is wot I expect.


Now I apply this code

[color=darkblue]Sub[/color] Evaluate_VLOOKUP()[color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
[color=darkblue]Dim[/color] rngCC [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] rngCC = ThisWorkbook.Worksheets("sheet1").Range("C3:C10")
[color=darkblue]Let[/color] rngCC = Evaluate("if(row(3:10),VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE))")
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_VLOOKUP()[/color]

…but get the following:-

Book1
	A	B	C	D	E
1	Produnt
2	Name
3	Chocolate-europe aroma	4	4	Choc	Choc
4	Chocolate-Cookies	0	4	Choc	Choc
5	Banana-Chocolate-Split	10	4	Bana	Bana
6	Limette-Ksekuchen	16	4	Lime	Lime
7	Erdbeere-Quark	8	4	Erdb	Erdb
8	Erdbeere-Mix	0	4	Erdb	Erdb
9	Jamaica Sun	6	4	Jama	Jama
10	Waldbeeren	0	4	Wald	Wald
11
12
Sheet1

. I would have expected that result without The extra If Row() stuff, which I thought overcame this problem.
. can anyone suggest wot is going wrong, or how I obtain the correct results (Using the Evaluate Function for a range)
Thanks
Alan.

P.s. I will also post this Thread Here: Multiple Columns Into Single Column Using Data Text To Column - Page 2

 

[DocAElstein]

There is a limit to how much memory Excel can use and what it can use it for - see Charles' page here: Excel Memory Limits - Decision Models

Thanks. Reading that now.
They seem to be talking about memory limits orders of magnitude bigger than wot i (think?) my arrays need?
Not sure if this is relevant......
My error messages say nothing about memory. Wot did yours say when it bombed out? (My translated error messages are mostly as before.. error ‘1004’ - something about not finding an element with the given name. I think I had an "application error” also once)

P.s. I am mostly using XL 2007. Just tried on XL 2010 ... bombed out at about 3973. But as I mentioned previously sometimes I get different results.
My inexperienced gut feeling is that it is not a memory problem, (but based on previous experience with you if you say it is then it probably is!)

 

[RoryA]

The error message was the same for me. The fact that I can't run the same code on the same data as you without errors, but it works if I reduce the amount of data, indicates a memory problem to me.

(but based on previous experience with you if you say it is then it probably is!)

I concur.

 

[DocAElstein]

Hi shg….

I'm more likely to read your code in future posts if it's in code tags………Four-point font in Technicolor attracts me not.

I'm right in the middle of reading War and Peace -- I'll read the rest of the thread when I finish that. Or sooner, if you provide a simple, clear, compact, monochrome description of the problem.

….In my rush late last night to prepare that post for you I forgot You do not like the colors. Sorry. I had meant to always provide you with b/w only. Sorry if that annoyed.
Alan
P.s.

…. At a glance, you can't have a lookup value > 255 characters, as I already explained. Good luck, enjoy the snow.

. One reason we were considering the last formulas was to overcome this limitation…. Which the last formula does… hence for demonstration purposes one item had a very long name, which i guess again made it look unnecessary complicated in the post I extra for you hurriedly prepared. Sorry again (-The snow was slowing me down!).

……………………………………………………………………………………………………………………………….

Rory,

.1

…. it works if I reduce the amount of data, …..

… Wot did you do to reduce the data?

.2 …… I tried the following method to reduce the data: I modified the code slightly to allow me to change the Rows in the Look Up Table and the rows in the Look Up values:

[color=darkblue]Sub[/color] Evaluate_INDEXwithMATCHMegaMegaRoryA7()
[color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] TheEnd
Application.ScreenUpdating = [color=darkblue]False[/color]
Application.Calculation = xlCalculationManual
 
    [color=darkblue]Dim[/color] wkstBlc [color=darkblue]As[/color] Worksheet, wkstPro [color=darkblue]As[/color] Worksheet [color=green]'Main File Worksheet, File with entries Worksheet[/color]
    [color=darkblue]Dim[/color] Blanco [color=darkblue]As[/color] [color=darkblue]String[/color], LookUpTable [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Let[/color] Blanco = "VBA Evaluate Range VLOOKUP and INDEX witm MATCH.xlsm"
    [color=darkblue]Set[/color] wkstBlc = Workbooks(Blanco).Worksheets.Item(5) [color=green]'fifth sheet in main File[/color]
    [color=darkblue]Let[/color] LookUpTable = "ABCDEFGHIJKLMNO.xlsx"
    [color=darkblue]Set[/color] wkstPro = Workbooks(LookUpTable).Worksheets("LookUpTable") [color=green]'First sheet in File with entries[/color]
       
    [color=darkblue]Dim[/color] lLastRow [color=darkblue]As[/color] [color=darkblue]Long[/color], NLastRow [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'[/color]
    [color=darkblue]Let[/color] lLastRow = 7000 '(3408  2370)  Maximum Look Up Table Row Number
    [color=darkblue]Let[/color] NLastRow = 800 [color=green]'         Maximum Look Up Value Table Row Number[/color]
    [color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngName = wkstBlc.Range("A3:A" & NLastRow)
   
    [color=darkblue]Dim[/color] rngLL [color=darkblue]As[/color] Range, rngJJ [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngLL = wkstBlc.Range("L3:L" & NLastRow)
    [color=darkblue]Set[/color] rngJJ = wkstBlc.Range("J3:J" & NLastRow)
   
    [color=darkblue]Let[/color] rngJJ = ""
    [color=darkblue]Let[/color] rngLL = ""
  
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX(" & wkstPro.Range("A16:C" & lLastRow).Address(External:=True) & ",N(IF(1,MATCH(" & rngName.Address & "," & wkstPro.Range("A16:A" & lLastRow).Address(External:=True) & ",0))),3),)")
 
    [color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX(" & wkstPro.Range("A16:C" & lLastRow).Address(External:=True) & ",N(IF(1,MMULT(N(TRANSPOSE(" & wkstPro.Range("A16:A" & lLastRow).Address(External:=True) & ")=" & rngName.Address & "),ROW(" & wkstPro.Range("A16:A" & lLastRow).Address(External:=True) & ")-ROW(" & wkstPro.Range("A15").Address(External:=True) & ")))),3),)")
TheEnd:
Application.ScreenUpdating = [color=darkblue]True[/color]
Application.Calculation = xlCalculationAutomatic
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_INDEXwithMATCHMegaMegaRoryA7()[/color]

. I was then able to achieve my 7000 Rows in the Look Up Table if I only used 800 Look Up Values in the Look Up Values Table (Note that strangely my original combined Row limit of 3408 had changed for no apparent reason as I began this experiment to 2370 ?!?! I have noticed this strange behavior before!)

. 3. Your Penultimate Code (the one I have been using in Column JJ) has always worked consistently for me to 7000. We originally went over to the final code as that penultimate one did not strangely overcome the 255 character limit. Working a bit backwards, can you think of any reason why that did not work (to overcome the 255 character limit, or whether a some slightly modified version of that should work: After all the original version of that formula was a variant on published formulas where specifically INDEX with MATCH was suggested as an alternative to VLOOKUP to overcome the 255 character limit. (Hope that is clear … I explained that point in greater length in that other thread http://www.mrexcel.com/forum/excel-...-value-255-character-limit-2.html#post3953521 )

.. No rush. Any comments or new ideas for “Mega” formulas appreciated when you have time.
Alan.

 

[shg]

No problem, glad Rory is sorting it out. You're in good hands.

 

[RoryA]

specifically INDEX with MATCH was suggested as an alternative to VLOOKUP to overcome the 255 character limit. (Hope that is clear … I explained that point in greater length in that other thread http://www.mrexcel.com/forum/excel-...-value-255-character-limit-2.html#post3953521 )

You missed the fundamental point about the versions that work with longer values - they do not pass those values to MATCH or VLOOKUP. They use them in a straight equality test. The value passed to MATCH in those cases is simply TRUE which is matched against an array of booleans and therefore the 255 character limit is immaterial.

Re #1 I simply changed the lLastRow variable to a smaller value.

 

[DocAElstein]

You missed the fundamental point about the versions that work with longer values - they do not pass those values to MATCH or VLOOKUP. They use them in a straight equality test. The value passed to MATCH in those cases is simply TRUE which is matched against an array of booleans and therefore the 255 character limit is immaterial.........

Hi
Sorry if i am missing the point again....But if MATCH is limiting to 255, How/ why is the INDEX with MATCH as alternative to VLOOKUP given in the published literature working at all.... Is that because in the original formula often given in the literature which does work .........

( =INDEX($A$16:$C$33,MATCH(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0),3) )
(which you were unable to convert to a Range Evaluate Mega one liner)

there is something more subtle going on compared with my basic formula
=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)
(which you were able to convert for me as the penultimate Mega formula)

. Sorry to be a pain but you can see to a novice it is very confusing. I think I am almost there. With your help I will get there and hopefully make a good "beginners explanation" when I get there to conclude wot I think is a very good and informative thread

...
Thanks for your patience. I am learning lots from you and will usee that knowledge (hopefully soon) to give something back
Alan

(P.s. after a re boot today after my compuer crashed.... I got to 4700 in my code before it bombed out?!?!.. wiered!)

 

[DocAElstein]

Hi
Sorry if i am missing the point again....But if MATCH is limiting to 255, How/ why is the INDEX with MATCH as alternative to VLOOKUP given in the published literature working at all.... Is that because in the original formula often given in the literature which does work .........

( =INDEX($A$16:$C$33,MATCH(TRUE,INDEX($A$16:$A$33=$A$3:$A$10,0),0),3) )
(which you were unable to convert to a Range Evaluate Mega one liner)

there is something more subtle going on compared with my basic formula
=INDEX($A$16:$C$33,MATCH($A$3:$A$10,$A$16:$A$33,0),3)
(which you were able to convert for me as the penultimate Mega formula)

. Sorry to be a pain but you can see to a novice it is very confusing. I think I am almost there. With your help I will get there and hopefully make a good "beginners explanation" when I get there to conclude wot I think is a very good and informative thread

...
Thanks for your patience. I am learning lots from you and will usee that knowledge (hopefully soon) to give something back
Alan

(P.s. after a re boot today after my compuer crashed.... I got to 4700 in my code before it bombed out?!?!.. wiered!)

P.s. maybe the penny is already dropping with me... My formula does not pass true maybe? ....

 

[RoryA]

As I said, in the second one the first argument to MATCH is the cells containing the long text - that doesn't work. In the first one, the argument is TRUE which is clearly not over 255 characters.

 

[DocAElstein]

P.s. maybe the penny is already dropping with me... My formula does not pass true maybe? ....

As I said, in the second one the first argument to MATCH is the cells containing the long text - that doesn't work. In the first one, the argument is TRUE which is clearly not over 255 characters.

-.. hey.. for once I got the point already....

 

[DocAElstein]

Things like This…

…. As I said, in the second one the first argument to MATCH is the cells containing the long text - that doesn't work. In the first one, the argument is TRUE which is clearly not over 255 characters. …..

….And the original Thread info before started with PGC and endlessly experimented with me in the Test area which helps to see it….

…..“A formula or part of a formula is immediately evaluated (calculated), even in manual calculation mode, when you…. For example……

Select the formula in the formula bar and press F9 (press ESC to undo and revert to the formula),….”[/QUOTE]

Is exactly the missing link in understanding which are missed out in all the books and other links I have endlessly gone through. It demonstrate how valuable this Forum is and in particular the experience and help given by people like PGC and RoryA. Thanks guys. And sorry I am a bit slow at getting the point.. We cannot all be geniuses like you!