VBA Evaluate Range and VLOOKUP

DocAElstein

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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
ABCD
1Produnt
2Name
3Chocolate-europe aroma4Choc
4Chocolate-Cookies0Choc
5Banana-Chocolate-Split10Bana
6Limette-Ksekuchen16Lime
7Erdbeere-Quark8Erdb
8Erdbeere-Mix0Erdb
9Jamaica Sun6Jama
10Waldbeeren0Wald
11
12
13
14LOOKUP Table
15Product Name
16Haselnu-Walnu-aromatisiert
17Tiramisu2
18Chocolate-colonial blend
19Chocolate-europe aroma4
20Chocolate-Cookies
21Jamaica Sun6
22Himbeere-Joghurt
23Erdbeere-Quark8
24Erdbeere-Mix
25Banana-Chocolate-Split10
26Waldbeeren
27Kirsche12
28Kirsche-grner Apfel
29Kirsche-Ananas14
30Stracciatella
31Limette-Ksekuchen16
32grner Apfel-Quark
33Blutorange-Quark
Sheet1
Cell Formulas
RangeFormula
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

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
ABCDE
1Produnt
2Name
3Chocolate-europe aroma4ChocChoc
4Chocolate-Cookies0ChocChoc
5Banana-Chocolate-Split10BanaBana
6Limette-Ksekuchen16LimeLime
7Erdbeere-Quark8ErdbErdb
8Erdbeere-Mix0ErdbErdb
9Jamaica Sun6JamaJama
10Waldbeeren0WaldWald
11
12
Sheet1


Which again is wot I expect.


Now I apply this code

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
ABCDE
1Produnt
2Name
3Chocolate-europe aroma44ChocChoc
4Chocolate-Cookies04ChocChoc
5Banana-Chocolate-Split104BanaBana
6Limette-Ksekuchen164LimeLime
7Erdbeere-Quark84ErdbErdb
8Erdbeere-Mix04ErdbErdb
9Jamaica Sun64JamaJama
10Waldbeeren04WaldWald
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
 
Sort by date Sort by votes
shg said:
There is no row limit for lookups.
Click to expand...

Yep.. Got that bit , thanks;)

. Must be some other Spanner in the works
Thanks Again..

P.s.
The VLOOKUP Mega code from Rory gives me no limit.. I am just having trouble with the second of the two INDEX codes he gave me..
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
Hi, shg....
shg said:
I'm right in the middle of reading War and Peace -- I'll re........
Click to expand...

I am right in the middle of snow in the Bavaria Mountains!.....Hope the telephone lines hold out!

....
OK..Thanks for taking the time and your patience!
. The thread has been going for some time, so I understand it is a bit confusing you seeing it the first time. Sorry.
. I will try a shortened version for you of Post #55 from this morning…

Here is part of an example Look Up Table.


Book1
ABCD
12
13
14LOOKUP Table
15Product Name
16Haselnu-Walnu-aromatisiert
17Tiramisu2
18Chocolate-colonial blend
19Chocolate-europe aroma4
20Chocolate-Cookies
21Jamaica Sun6
22Himbeere-Joghurt
23Erdbeere-Quark8
24Erdbeere-Mix
25SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 186/15/9/3,7/ / / /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4////10
26Waldbeeren
27Kirsche12
28Kirsche-grner Apfel
29Kirsche-Ananas14
30Stracciatella
31Limette-Ksekuchen16
32grner Apfel-Quark
33Blutorange-Quark
34Row34C34
35Row35C35
36Row36C36
37Row37C37
38Row38C38
LookUpTable


And here is the file (XL 2007 ) : ABCDEFGHIJKLMNO.xlsx
https://app.box.com/s/jnjrtpa1saqhu2vb70po

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

Here is part of the corresponding Look Up value file before..


Book1
AL
1ProduntMega Rory2
2Name (Look Up Values)Range Evaluate INDEX with MATCH
3Chocolate-europe aroma
4Chocolate-Cookies
5SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 186/15/9/3,7/ / / /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4////
6Limette-Ksekuchen
7Erdbeere-Quark
8Erdbeere-Mix
9Jamaica Sun
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34Row34
35Row35
36Row36
LookUpValues



And after running the macro…


Book1
AL
1ProduntMega Rory2
2Name (Look Up Values)Range Evaluate INDEX with MATCH
3Chocolate-europe aroma4
4Chocolate-Cookies
5SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 186/15/9/3,7/ / / /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4////10
6Limette-KsekuchenC1590
7Erdbeere-Quark8
8Erdbeere-Mix
9Jamaica Sun6
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34Row34C34
35Row35C35
36Row36C36
LookUpValues


Here is the code

Code: 
[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]
 
[color=darkblue]Sub[/color] Evaluate_INDEXwithMATCHMegaMegaRoryforshgA7()
[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 for shg.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] [color=green]'[/color]
    lLastRow = 3408 '3408
    [color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngName = wkstBlc.Range("A3:A" & lLastRow)
   
    [color=darkblue]Dim[/color] rngLL [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngLL = wkstBlc.Range("L3:L" & lLastRow)
 
    [color=darkblue]Let[/color] rngLL = ""
  
   
 
    [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_INDEXwithMATCHMegaMegaRoryforshgA7()[/color]


The code is in Module “MacroForshg” in this file: (XL 2007) VBA Evaluate Range VLOOKUP and INDEX witm MATCH for shg.xlsm
https://app.box.com/s/4fvdyd23ri6a7vr45nj0

(The file should open in the appropriate sheet (Sheet 5 “LookUpValues”) )

It is basically doing wot I want (The strange result in Row 6 is a strange new problem, not part of my main problem). The main problem is that the code does not seem to work for more than about 3400 Rows. I need it to work for about 7000. In other words if in the code you change lLastRow to a number greater than I have currently set it (3408), then the code does not work.
. Hope that is a bit clearer. It is basically just a shortened version for you from post #55.


Thanks,
Alan Elston
 
Upvote 0
Doc, I'm just not going to read all of that. At a glance, you can't have a lookup value > 255 characters, as I already explained. Good luck, enjoy the snow.
 
Upvote 0
I think you're simply running into a memory issue. Bear in mind that you are attempting some pretty complicated array formula calculations on a large range of data.
 
Upvote 0
RoryA said:
I think you're simply running into a memory issue. Bear in mind that you are attempting some pretty complicated array formula calculations on a large range of data.
Click to expand...


OK, thanks Rory.

. I had had a long break with computers until recently, and thought in the meantime running out of memory was nowadays almost never an issue?
…….
. I just took a look at an actual file of mine which was also just working to about 3401 rows with your last Mega Formula. I was also there coincidentally for initial testing just using 3 columns for the Look Up table (A – C), which is the same as I have been using in the simplified example that I prepared for this Thread.
…..I just increased that to A-AL which would include a Look Up Table Array with considerably more data.

Code: 
Let rngCC.Value = Evaluate("INDEX(INDEX(" & wkstPro.Range("[COLOR=#ff0000]A3:AL[/COLOR]" & lLastRow).Address(External:=True) & ",N(IF(1,MMULT(N(TRANSPOSE(" & wkstPro.Range("A3:A" & lLastRow).Address(External:=True) & ")=" & rngName.Address & "),ROW(" & wkstPro.Range("A3:A" & lLastRow).Address(External:=True) & ")-ROW(" & wkstPro.Range("A2").Address(External:=True) & ")))),3),)")

…….. It made no difference to the 3401 Limit. But I guess it is more subtle than that.
. I guess if it is a memory problem then it is an allocated memory space to something in that formula rather than an actual computer limit?.

. Thanks again for all your help. Looks for now that I have hit a brick wall with that last formula. But an interesting one.

. If at any time any other slightly different version of that last formula springs to mind, please post it and I will Endeavour to do justice to it in trying it out (and maybe one day understanding how it works!):
It seems a shame that I am in the right ball park with 3400, as I need 7000, so I do not need an order of Magnitude more. Maybe by chance a slight Variation in that formula would do the trick. I have no chance of coming up with such a formula. I am amazed how You do it?: As I have mentioned previously I have read and been told that such formulas are not possible.

. Alan
 
Upvote 0
Changing the first range won't make any difference since the formula only uses the third column.

I can't even use 3400 on my work PC - it bombs somewhere around 3200. You'd probably need 64bit Office to get near 7000.
 
Upvote 0
RoryA said:
Changing the first range won't make any difference since the formula only uses the third column.

I can't even use 3400 on my work PC - it bombs somewhere around 3200. You'd probably need 64bit Office to get near 7000.
Click to expand...

OK, that is very useful feedback****.. Many thanks for taking the time to try it out on your computer. So my assumption may be wrong, about an allocated memory space, and it may indeed be a computer memory problem. My Computers are a bit old. (Acer Aspires and old LG Notebooks all with 32 Bit Vista or XP). Can I do something simple like deleting lots to "free up space" or is it more subtle than that?


Alan

P.s.

**** If anyone out there with a better (64 Bit etc,) Computer can at some time try the code and let me know where it bombs out I think that would be extremely interesting. (All the info and files needed can be found in the summary I did for shg last night in Post #63

Here again the Look Up Table File (XL 2007 ) : ABCDEFGHIJKLMNO.xlsx
https://app.box.com/s/jnjrtpa1saqhu2vb70po

Here again the Look Up Value File: (XL 2007) <acronym>VBA</acronym> Evaluate Range VLOOKUP and INDEX witm MATCH for shg.xlsm
https://app.box.com/s/4fvdyd23ri6a7vr45nj0
The code is in Module “MacroForshg” in this file
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
How to vlookup the characters between a delimiter
Replies
3
Views
175
Fluff
Fluff
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
269
offthelip
O
Z
  • Solved
VBA: Convert Cell Contents into Hyperlink on Cell Change
Replies
5
Views
494
zero269
Z
P
  • Solved
How to vlookup a general format cell and convert it to a date
Replies
4
Views
244
Fluff
Fluff
J
  • Solved
VLookup Struggles
Replies
7
Views
284
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,537
Messages
6,120,096
Members
448,944
Latest member
SarahSomethingExcel100

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top