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
