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!) :-
I apply this code
And get the following:-
Which again is wot I expect.
Now I apply this code
…but get the following:-
. 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
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
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
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