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
RoryA said:
You would have to array enter that into all the relevant cells in column M at once.
Click to expand...


Hmm... might have to leave that one... getting into arrays which I do not understand....

...I copied
=INDEX(INDEX($C$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE($A$16:$A$33)=$A$3:$A$10),ROW($A$16:$A$33)-ROW($A$15))))),)
to the clipboard, highlighted cells M3 through to M10.....and then did CONTROL+SHIFT+ENTER...

.. but nothing happened??
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Select M3:M10, press f2, paste the formula in, then Ctrl+Shift+Enter.
 
Upvote 0
RoryA said:
Select M3:M10, press f2, paste the formula in, then Ctrl+Shift+Enter.
Click to expand...



............
Book1
N
1Rory2
2Spreadsheet (array) Formula
34
40
510
616
78
80
96
100
Sheet4
Cell Formulas
RangeFormula
N3:N10{=INDEX(INDEX($C$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE($A$16:$A$33)=$A$3:$A$10),ROW($A$16:$A$33)-ROW($A$15))))),)}
Press CTRL+SHIFT+ENTER to enter array formulas.




Thanks! ;)
 
Upvote 0
Yikes! Since I last looked you've thrown in another INDEX, another N, a TRANSPOSE and to top it all off an MMULT as well?!

And I thought my solutions were convoluted! :LOL:
 
Upvote 0
XOR LX said:
Yikes! Since I last looked you've thrown in another INDEX, another N, a TRANSPOSE and to top it all off an MMULT as well?!

And I thought my solutions were convoluted! :LOL:
Click to expand...


and I have fun as well converting them into German

How on earth does he come up with them. They always work.... BUT HOW!
 
Upvote 0
DocAElstein said:
Hi pgc ( and XOR LX),

I am very grateful for you both for your further inputs…….......have learnt a lot from participating in this thread. ……….Many thanks again…..Alan



P.s. PGC - Your following suggested method (which gives indication if there are array values from the formula available) was very interesting and useful.
. 1) selecting a cell with a formula in it and
. 2) then selecting (Highlighting) that formula in the Formula bar and
. 3) then pressing F9 to see a result of the form {4;0;10;etc} indicating if there are array values from the formula available

. - Can you please tell me which key combination returns the displayed result in the formula window back to the formula?
Click to expand...


Found this:

…..“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),….”
 
Upvote 0
Mega Code “External” Look Up Table problem
Hi Rory, (or anyone else viewing..)
…. Sorry for coming back with another question/ problem. I realize the codes were given with reservations as to their suitability considering it’s convoluted form. However I was having some interesting results that I was hoping to post as additional useful feedback in some other Threads. ..
…. However I have run into some inconsistent problems.. After a few days of head banging I thought I would try to explain one problem here, in case any hints can be given to where it is going wrong.

. To explain I again modify the files and codes to suit the simplified example used initially in this Thread.

. Here is my sheet before …


Book1
ABCJKL
1ProduntRory1Rory2
2Name (Look Up Value)Range Evaluate INDEX with MATCHRange 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
14LOOKUP Table
15Product NameQuantity
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
LookUpValues





…..and this is how it looks after running the Macro (Sub Evaluate_INDEXwithMATCHMegaMegaRoryA2() ) with The last 2 Mega “Evaluate Range Index with Match one liner codes” kindly given to me by RoryA in this Thread (Post #30 and #38 )


Book1
ABCJKL
1ProduntRory1Rory1Rory2
2NameRange Evaluate INDEX with MATCHSpesadsheet FormulaRange Evaluate INDEX with MATCH
3Chocolate-europe aroma44
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////#WERT!10
6Limette-Ksekuchen1616
7Erdbeere-Quark88
8Erdbeere-Mix
9Jamaica Sun66
10#NV
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
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
LookUpValues


.. Here the codes again:-

Code: 
[color=darkblue]Sub[/color] Evaluate_INDEXwithMATCHMegaMegaRoryA2()
 
    [color=darkblue]Dim[/color] rngName               [color=darkblue]As[/color] Range
 
   
    [color=darkblue]Set[/color] rngName = ActiveSheet.Range("$A$3:$A$10")
 
    [color=darkblue]Dim[/color] rngJJ [color=darkblue]As[/color] Range, rngLL [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngJJ = ActiveSheet.Range("J3:J10")
    [color=darkblue]Set[/color] rngLL = ActiveSheet.Range("L3:L10")
    [color=darkblue]Let[/color] rngJJ = ""
    [color=darkblue]Let[/color] rngLL = ""
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX($A$16:$C$33,N(IF(1,MATCH(" & rngName.Address & ",$A$16:$A$33,0))),3),)")
 
    [color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX($A$16:$C$33,N(IF(1,MMULT(N(TRANSPOSE(A16:A33)=" & rngName.Address & "),ROW(A16:A33)-ROW(A15)))),3),)")
   
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_INDEXwithMATCHMegaMegaRoryA2()[/color]

. So far so good (and I have had many days of “pleasure” in English and German with files that use variants in code and spreadsheet forms of the Mega Evaluate one liners!).
. Originally for simplicity in this Thread I kept my Look Up Values and Look Up Table on the same sheet

.. But:

. I sometimes*** run into a problem if I try to reference a Look Up Table on another sheet or another file.
. For example the codes here ( Sub Evaluate_INDEXwithMATCHMegaMegaRoryA3() ) are slightly modified to reference the following Look Up Table on a second sheet (Sheet “LookUpTable”):


Book1
ABC
10
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
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
LookUpTable




. here the codes:

Code: 
[color=darkblue]Sub[/color] Evaluate_INDEXwithMATCHMegaMegaRoryA3()
 
    [color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range, rngLUpValues [color=darkblue]As[/color] Range, rngLUpEntries [color=darkblue]As[/color] Range, rngLUpOffset [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngLUpValues = Worksheets("LookUpTable").Range("$A$16:$C$33")
    [color=darkblue]Set[/color] rngLUpEntries = Worksheets("LookUpTable").Range("$A$16:$A$33")
    [color=darkblue]Set[/color] rngLUpOffset = Worksheets("LookUpTable").Range("$A$15")
   
    [color=darkblue]Set[/color] rngName = ActiveSheet.Range("$A$3:$A$10")
 
    [color=darkblue]Dim[/color] rngJJ [color=darkblue]As[/color] Range, rngLL [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngJJ = ActiveSheet.Range("J3:J10")
    [color=darkblue]Set[/color] rngLL = ActiveSheet.Range("L3:L10")
    [color=darkblue]Let[/color] rngJJ = ""
    [color=darkblue]Let[/color] rngLL = ""
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX(" & rngLUpValues.Address & ",N(IF(1,MATCH(" & rngName.Address & "," & rngLUpEntries.Address & ",0))),3),)")
 
    [color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX(" & rngLUpValues.Address & ",N(IF(1,MMULT(N(TRANSPOSE(" & rngLUpEntries.Address & ")=" & rngName.Address & "),ROW(" & rngLUpEntries.Address & ")-ROW(" & rngLUpOffset.Address & ")))),3),)")
   
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_INDEXwithMATCHMegaMegaRoryA3()[/color]

. Note: Originally I mistakingly thought this was working. But after removing the Look Up Table in the first sheet***, it was clearly not working and for some strange reason was still trying to reference the Look Up Table in the first sheet!? Here the results from running the second codes:


Book1
ABCJKL
1ProduntRory1Rory1Rory2
2NameRange Evaluate INDEX with MATCHSpesadsheet FormulaRange 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////#WERT!
6Limette-Ksekuchen
7Erdbeere-Quark
8Erdbeere-Mix
9Jamaica Sun
10#NV
11
12
13
14LOOKUP Table
15Product Name
16Haselnu-Walnu-aromatisiert
17Tiramisu
18Chocolate-colonial blend
19Chocolate-europe aroma
20Chocolate-Cookies
21Jamaica Sun
22Himbeere-Joghurt
23Erdbeere-Quark
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////
26Waldbeeren
27Kirsche
28Kirsche-grner Apfel
29Kirsche-Ananas
30Stracciatella
31Limette-Ksekuchen
32grner Apfel-Quark
33Blutorange-Quark
LookUpValues
.

(. Note the values are removed from the look Up table in the first sheet otherwise strangely the codes work!?***)



. After some effort I hit on an Argument
Code: 
(External:=True)
which I included in the next codes ( Sub Evaluate_INDEXwithMATCHMegaMegaRoryA4() ):

Code: 
[color=darkblue]Sub[/color] Evaluate_INDEXwithMATCHMegaMegaRoryA4()
 
    [color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range, rngLUpValues [color=darkblue]As[/color] Range, rngLUpEntries [color=darkblue]As[/color] Range, rngLUpOffset [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngLUpValues = Worksheets("LookUpTable").Range("$A$16:$C$33")
    [color=darkblue]Set[/color] rngLUpEntries = Worksheets("LookUpTable").Range("$A$16:$A$33")
    [color=darkblue]Set[/color] rngLUpOffset = Worksheets("LookUpTable").Range("$A$15")
   
    [color=darkblue]Set[/color] rngName = ActiveSheet.Range("$A$3:$A$10")
 
    [color=darkblue]Dim[/color] rngJJ [color=darkblue]As[/color] Range, rngLL [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngJJ = ActiveSheet.Range("J3:J10")
    [color=darkblue]Set[/color] rngLL = ActiveSheet.Range("L3:L10")
    [color=darkblue]Let[/color] rngJJ = ""
    [color=darkblue]Let[/color] rngLL = ""
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX(" & rngLUpValues.Address(External:=True) & ",N(IF(1,MATCH(" & rngName.Address & "," & rngLUpEntries.Address(External:=True) & ",0))),3),)")
 
    [color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX(" & rngLUpValues.Address(External:=True) & ",N(IF(1,MMULT(N(TRANSPOSE(" & rngLUpEntries.Address(External:=True) & ")=" & rngName.Address & "),ROW(" & rngLUpEntries.Address(External:=True) & ")-ROW(" & rngLUpOffset.Address(External:=True) & ")))),3),)")
   
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_INDEXwithMATCHMegaMegaRoryA4()[/color]

. It appeared to cure the problem in the first one liner, but not the second as shown in the following results after running the above codes:


Book1
ABCJKL
1ProduntRory1Rory1Rory2
2NameRange Evaluate INDEX with MATCHSpesadsheet FormulaRange Evaluate INDEX with MATCH
3Chocolate-europe aroma4#WERT!
4Chocolate-Cookies#WERT!
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////#WERT!#WERT!
6Limette-Ksekuchen16#WERT!
7Erdbeere-Quark8#WERT!
8Erdbeere-Mix#WERT!
9Jamaica Sun6#WERT!
10#NV#WERT!
11
12
13
14LOOKUP Table
15Product Name
16Haselnu-Walnu-aromatisiert
17Tiramisu
18Chocolate-colonial blend
19Chocolate-europe aroma
20Chocolate-Cookies
21Jamaica Sun
22Himbeere-Joghurt
23Erdbeere-Quark
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////
26Waldbeeren
27Kirsche
28Kirsche-grner Apfel
29Kirsche-Ananas
30Stracciatella
31Limette-Ksekuchen
32grner Apfel-Quark
33Blutorange-Quark
LookUpValues




. Just for completeness, (and to get closer to the real life requirement) I included the following code ( Sub Evaluate_INDEXwithMATCHMegaMegaRoryA5() ) that references the same Look Up Table but in another workbook ( Workbook Here, (XL 2007 .xlsx) : https://app.box.com/s/k2blsoyjkbomjfb87o7r )
. Here are the codes:

Code: 
[color=darkblue]Sub[/color] Evaluate_INDEXwithMATCHMegaMegaRoryA5()
 
    [color=darkblue]Dim[/color] rngName [color=darkblue]As[/color] Range, rngLUpValues [color=darkblue]As[/color] Range, rngLUpEntries [color=darkblue]As[/color] Range, rngLUpOffset [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngLUpValues = Workbooks("ExternalLookUpTable").Worksheets("LookUpTable").Range("$A$16:$C$33")
    [color=darkblue]Set[/color] rngLUpEntries = Workbooks("ExternalLookUpTable").Worksheets("LookUpTable").Range("$A$16:$A$33")
    [color=darkblue]Set[/color] rngLUpOffset = Workbooks("ExternalLookUpTable").Worksheets("LookUpTable").Range("$A$15")
   
    [color=darkblue]Set[/color] rngName = ActiveSheet.Range("$A$3:$A$10")
 
    [color=darkblue]Dim[/color] rngJJ [color=darkblue]As[/color] Range, rngLL [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] rngJJ = ActiveSheet.Range("J3:J10")
    [color=darkblue]Set[/color] rngLL = ActiveSheet.Range("L3:L10")
    [color=darkblue]Let[/color] rngJJ = ""
    [color=darkblue]Let[/color] rngLL = ""
    [color=darkblue]Let[/color] rngJJ.Value = Evaluate("INDEX(INDEX(" & rngLUpValues.Address(External:=True) & ",N(IF(1,MATCH(" & rngName.Address & "," & rngLUpEntries.Address(External:=True) & ",0))),3),)")
 
    [color=darkblue]Let[/color] rngLL.Value = Evaluate("INDEX(INDEX(" & rngLUpValues.Address(External:=True) & ",N(IF(1,MMULT(N(TRANSPOSE(" & rngLUpEntries.Address(External:=True) & ")=" & rngName.Address & "),ROW(" & rngLUpEntries.Address(External:=True) & ")-ROW(" & rngLUpOffset.Address(External:=True) & ")))),3),)")
   
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'Evaluate_INDEXwithMATCHMegaMegaRoryA5()[/color]

. Again the second code does not work giving

Book1
ABCJKL
1ProduntRory1Rory1Rory2
2NameRange Evaluate INDEX with MATCHSpesadsheet FormulaRange Evaluate INDEX with MATCH
3Chocolate-europe aroma4#WERT!
4Chocolate-Cookies#WERT!
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////#WERT!#WERT!
6Limette-Ksekuchen16#WERT!
7Erdbeere-Quark8#WERT!
8Erdbeere-Mix#WERT!
9Jamaica Sun6#WERT!
10#NV#WERT!
11
12
13
14LOOKUP Table
15Product Name
16Haselnu-Walnu-aromatisiert
17Tiramisu
18Chocolate-colonial blend
19Chocolate-europe aroma
20Chocolate-Cookies
21Jamaica Sun
22Himbeere-Joghurt
23Erdbeere-Quark
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////
26Waldbeeren
27Kirsche
28Kirsche-grner Apfel
29Kirsche-Ananas
30Stracciatella
31Limette-Ksekuchen
32grner Apfel-Quark
33Blutorange-Quark
LookUpValues


. Any help would be greatly appreciated on solving this specific problem, (or even some new “guesses” for different versions of the second “Mega- one liner Evaluate code”, the convoluted form of which I am beginning to think is based on very long intuitive experience rather than clear logic!)
Thanks

Alan Elston

. P.s. 1 The complete File with Macros of interest here in Module “ExternalLookUpTableProb” and also duplicated in the Sheet Module “LookUpValues”
https://app.box.com/s/olpz5cp7fhn1pjgu297n

. P.s. I did have a file working with externally referencing the Look Up Table, but there were other bigger problems with that file making it inappropriate for demonstration purposes. I shall in the meantime continue to look at that to see if I can solve the mysterious problem I have here. I shall report back any findings.
 
Upvote 0
It's the thread that keeps on giving...


Evaluate is limited to a formula length of ca. 255 characters. If you use the External:=True parameter you are including the full workbook name in the formula string, when all you need is the sheet name. That last is why the original didn't work - Application.Evaluate always evaluates in the context of the active sheet, so if you don't supply sheet references, your ranges all need to be on the same sheet. Worksheet.Evaluate evaluates in the context of the specified sheet whether or not it is active.
 
Upvote 0
QUOTE=RoryA;3974473]It's the thread that keeps on giving...


Evaluate is limited to a formula length of ca. 255 characters. If you use the External:=True parameter you are including the full workbook name in the formula string, when all you need is the sheet name. That last is why the original didn't work - Application.Evaluate always evaluates in the context of the active sheet, so if you don't supply sheet references, your ranges all need to be on the same sheet. Worksheet.Evaluate evaluates in the context of the specified sheet whether or not it is active.[/QUOTE]

Hi Rory,
. Thanks very much for the continued help. I very much appreciate it.
. I have been going crazy all day trying to pull apart my files that are working and find the difference to those that do not. At one point I had very similar example files to those in this thread working….then my computer just crashed under the strain!
. It may have had something to do with the sheet referencing, - so I will bare your comments in mind and keep battling to understand exactly where the problem lies, that is to say how to change files such as those not working in order for them to work!! (Once I have the computer working again!!!)
Alan.

P.s. If you did have time change those last codes to work (but referencing the external Look Up Table File), you might save my brain “crashing under the strain”.. But I appreciate you have already given a lot here and I will keep at it and report back if and when I get there!!

Thanks very much again.
Alan
 
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
185
Fluff
Fluff
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
283
offthelip
O
Z
  • Solved
VBA: Convert Cell Contents into Hyperlink on Cell Change
Replies
5
Views
516
zero269
Z
P
  • Solved
How to vlookup a general format cell and convert it to a date
Replies
4
Views
250
Fluff
Fluff
J
  • Solved
VLookup Struggles
Replies
7
Views
289
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,932
Messages
6,122,331
Members
449,077
Latest member
jmsotelo

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