VBA Evaluate Range and VLOOKUP

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!) :-


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Bana</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Lime</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-right: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Jama</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-right: 1px solid black;;">Waldbeeren</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Wald</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;;">LOOKUP Table</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;border-left: 1px solid black;;">Product Name</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;border-left: 1px solid black;;">Haselnuß-Walnuß-aromatisiert</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;border-left: 1px solid black;;">Tiramisu</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">2</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-colonial blend</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">4</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;border-left: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;border-left: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">6</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;border-left: 1px solid black;;">Himbeere-Joghurt</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;border-left: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">8</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;border-left: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;border-left: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">10</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;border-left: 1px solid black;;">Waldbeeren</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">12</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche-grüner Apfel</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;border-left: 1px solid black;;">Kirsche-Ananas</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">14</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;border-left: 1px solid black;;">Stracciatella</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;border-left: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;">16</td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;border-left: 1px solid black;;">grüner Apfel-Quark</td><td style="text-align: center;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;">Blutorange-Quark</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A3,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A4,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A5,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A6,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A7,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A8,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A10,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=LEFT(<font color="Blue">A3,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=LEFT(<font color="Blue">A4,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=LEFT(<font color="Blue">A5,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=LEFT(<font color="Blue">A6,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=LEFT(<font color="Blue">A7,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=LEFT(<font color="Blue">A8,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=LEFT(<font color="Blue">A9,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D10</th><td style="text-align:left">=LEFT(<font color="Blue">A10,4</font>)</td></tr></tbody></table></td></tr></table><br />



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:-

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Bana</td><td style="text-align: center;;">Bana</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Lime</td><td style="text-align: center;;">Lime</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-right: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Jama</td><td style="text-align: center;;">Jama</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-right: 1px solid black;;">Waldbeeren</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;;">Wald</td><td style="text-align: center;;">Wald</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

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:-


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Choc</td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Bana</td><td style="text-align: center;;">Bana</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Lime</td><td style="text-align: center;;">Lime</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Erdb</td><td style="text-align: center;;">Erdb</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-right: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Jama</td><td style="text-align: center;;">Jama</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-right: 1px solid black;;">Waldbeeren</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;;">Wald</td><td style="text-align: center;;">Wald</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

. 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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,737
Hi Alan

Interesting question.

First, the way I'd do it would be, for ex., using your formula

Code:
With rngCC
         .Formula = "=VLOOKUP(" & rngName(1, 1).Address(0, 0) & ",$A$16:$C$33,3,FALSE)"
         .Value = .Value
End With
or calling VlookUp() in vba:

Code:
rngCC = Application.WorksheetFunction.VLookup(rngName, Range("A16:C33"), 3, False)
. 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)
I can give you my take on this.


The way I see it, you are not getting the results you want simply because they are not there.

The "if(row()" or "if(column()" workarounds in the Evaluate do not create answers, they just remind the vba Evaluate() to get all the results that the formula in the worksheet returns and not just one.

If the results are not there you cannot get them.

I'll try to explain what I mean with 2 examples.

Example 1 - the evaluation of the formula returns an array

Some formulas may return an array when you evaluate them in the worksheet but when you use Evaluate() in vba you just get 1.

An example. Let's say B1 holds a Text value and B2 a number value. Write in A1:

=ISTEXT(B1:B2)

Although you only get in the cell 1 result (True/False), you are examining 2 cells, and if in the formula bar you select the formula and press F9 you see that the result is, in fact, the array {TRUE;FALSE}. You are seeing 1 result in the worksheet cell but the formula generated an array with 2 results.

Now if in vba you use:

Code:
Dim v As Variant

v = Evaluate("ISTEXT(B1:B2)")
If you execute this statement and inspect v you'll see that it is Variant/Boolean with the value True.

In this case you already saw in the worksheet that the formula returns an array. To tell Evaluate() to loop through the range you use the "if(row()". This way, since row() always returns an array, Evaluate() will go through all the values.

Code:
Dim v As Variant

v = Evaluate("IF(ROW(B1:B2),ISTEXT(B1:B2))")
Now if you inspect v you see it's a Variant/Variant(1 to 2, 1 to 1) and you get the array with the values True and False

The "if(row()" did not create the other result, it just helped bringing it back.


Example 2 - the evaluation of the formula does not return an array, it returns a simple value

This is the case for ex. of

=VLOOKUP(A1:A2,B1:C3,2,FALSE)

Now if you add this formula to a cell with some values in A1:A2 and B1:C3 you'll see 1 value in the cell.

If you now do as in the previous example and select the formula in the formula bar and press F9 you'll see that the formula only returns that 1 value. It does not return an array although you might think it would since the first parameter is an array.


Now this is my point. When, like in this case, the formula does not return an array there's no use in Evaluating in vba using the "If(Row()".

There is no other value to get. You'll just get the same value twice

If you try:

Code:
Dim v As Variant

v = Evaluate("=VLOOKUP(A1:A2,B1:C3,2,FALSE)")
v = Evaluate("=IF(ROW(A1:A2),VLOOKUP(A1:A2,B1:C3,2,FALSE))")
and inspect v after each Evaluate() statement you'll see that exactly.

Notice that this would also be valid for others formula, like:
=INDEX(A1:A3,{1;3;2},1)

Also in this case the same as with the VLookUp(). If you evaluate the formula in the worksheet in the formula bar with F9 you see that there is only 1 value returned from the formula, so there's no point in using the If(row() in vba. You'll just get the same value thrice.


Conclusion:

If the formula evaluated in the worksheet returns an array of values but in vba is only returning 1 value, you can use "if(row()" or "if(column()" bits to tell vba to get all the results.

If the formula evaluated in the worksheet returns only 1 value that's all you can get


Remark: I don't mean by this that the formulas could not return more than one value, they could if used as array formulas that return an array, or using some workaround, but not as simple native formulas inserted directly in a cell like we did in these examples.

Please comment.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi pgc,

Re:- VBA Evaluate Range VLOOKUP

Hi Alan

Interesting question.

First, the way I'd do it ………
…..
. …………………….

I can give you my take on this…….
……
. …………….
Please comment.

. Thanks very much for taking the trouble to reply in such detail. (I thought this one had been lost and buried under the amazing number of threads that go through this forum!).
. I have to be away from my (Excel) computers just now. As soon as I can I will go carefully through everything you have kindly written and get back to you. (By replying (only) to this Thread/Post)

. Thanks Again
. Alan Elston
Bavaria
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi pgc,

Re:- VBA Evaluate Range VLOOKUP

….
First, the way I'd do it would be, for ex., using your formula


Code:
Sub Test3b_pgc()
 
Dim rngName As Range
Set rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
Dim rngCC As Range
Set rngCC = ThisWorkbook.Worksheets("sheet1").Range("C3:C10")
With rngCC
         .Formula = "=VLOOKUP(" & rngName(1, 1).Address(0, 0) & ",$A$16:$C$33,3,FALSE)"
'         .Value = .Value
End With
 
End Sub
….
…..
...
. This has been a learning exercise for me (Which maybe I tried to take a (5th) bridge too far for my beginner’s competence by looking at a further 5th Evaluate method!!). This thread followed on from thread http://www.mrexcel.com/forum/excel-questions/805285-copy-based-match-criteria-code-alternative-looping.html#post3937559 where the third method kindly suggested by Jerry Sullivan follows very closely your method suggested in the above code.

Code:
[color=darkblue]Sub[/color] Test3UsingR1C1JerrySullivan() [color=green]'enters R1C1 formula into results range then converts formulas to values[/color] [color=darkblue]Dim[/color] rngDD [color=darkblue]As[/color] Range
 [color=darkblue]Set[/color] rngDD = ThisWorkbook.Worksheets("sheet1").Range("D3:D10")
 [color=darkblue]Dim[/color] rngLOOKUP [color=darkblue]As[/color] Range
 [color=darkblue]Set[/color] rngLOOKUP = ThisWorkbook.Worksheets("sheet1").Range("$A$16:$C$33")


 [color=darkblue]With[/color] rngDD
   .FormulaR1C1 = "=VLOOKUP(R[0]C[-3]," & rngLOOKUP.Address(ReferenceStyle:=xlR1C1, External:=True) & ",3,0)"
   [color=green]'Jerry put my exact SVERWEISS formula in![/color]
   [color=green]'. Syntax: FormulaR1C1=" here the formula ". The [] makes it relative referrencing.[/color]
 [color=green]'  .Value = .Value 'Removes Formula(Puts value in)[/color]
 [color=darkblue]End[/color] [color=darkblue]With[/color]


[color=darkblue]End[/color] [color=darkblue]Sub[/color]

As that was my very first experience with the .Formula RC stuff, it is very helpful again to see a slightly different version of this method. A great helper again in my learning. Thanks!


….
…or calling VlookUp() in <acronym>vba</acronym>:

Code:
Sub Test3c_pgc()
 
Dim rngName As Range
Set rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
Dim rngEE As Range
Set rngEE = ThisWorkbook.Worksheets("sheet1").Range("E3:E10")
 
Let rngEE = Application.WorksheetFunction.VLookup(rngName, Range("A16:C33"), 3, False)
 
End Sub
….

...
… and once again a very helpful comparison. Many Thanks.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Alan</td><td style="text-align: center;color: #FF0000;;">pgc</td><td style="text-align: center;color: #FF0000;;">Jerry Sullivan</td><td style="text-align: center;color: #FF0000;;">pgcVBA</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;color: #FF0000;;">4</td><td style="text-align: center;color: #FF0000;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;color: #FF0000;;">0</td><td style="text-align: center;color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;color: #FF0000;;">10</td><td style="text-align: center;color: #FF0000;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-right: 1px solid black;;">Limette-Käsekuchen</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">16</td><td style="text-align: center;color: #FF0000;;">16</td><td style="text-align: center;color: #FF0000;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Quark</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">8</td><td style="text-align: center;color: #FF0000;;">8</td><td style="text-align: center;color: #FF0000;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-right: 1px solid black;;">Erdbeere-Mix</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;color: #FF0000;;">0</td><td style="text-align: center;color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-right: 1px solid black;;">Jamaica Sun</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">6</td><td style="text-align: center;color: #FF0000;;">6</td><td style="text-align: center;color: #FF0000;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-right: 1px solid black;;">Waldbeeren</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;color: #FF0000;;">0</td><td style="text-align: center;color: #FF0000;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A3,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A3,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A3,Sheet1!$A$16:$C$33,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A4,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A4,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A4,Sheet1!$A$16:$C$33,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A5,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A5,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A5,Sheet1!$A$16:$C$33,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A6,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A6,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A6,Sheet1!$A$16:$C$33,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A7,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C7</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A7,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A7,Sheet1!$A$16:$C$33,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A8,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C8</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A8,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A8,Sheet1!$A$16:$C$33,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,Sheet1!$A$16:$C$33,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A10,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A10,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A10,Sheet1!$A$16:$C$33,3,0</font>)</td></tr></tbody></table></td></tr></table><br />


All codes up to now in Sheet1 Module of Example File



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

….
… Example 1…..
Let's say B1 holds a Text value and B2 a number value. Write in A1:

=ISTEXT(B1:B2)

Although you only get in the cell 1 result (True/False), you are examining 2 cells, and if in the formula bar you select the formula and press F9 you see that the result is, in fact, the array {TRUE;FALSE}. You are seeing 1 result in the worksheet cell but the formula generated an array with 2 results……..
………etc……

Code:
Sub RowColumnTrick1()
 
Dim v As Variant
Let v = Evaluate("ISTEXT(B1:B2)")
Dim vIfRow() As Variant
vIfRow() = Evaluate("IF(Row(B1:B2),ISTEXT(B1:B2))")
Dim vIfColumn() As Variant
vIfColumn() = Evaluate("IF(Column(B1:B2),ISTEXT(B1:B2))")
End Sub
….
…etc…..
…. Interesting example to watch in watch window! Sort of shows about as far as I had got originally understanding the Row / Column trick Stuff! (The following is in sheet 2 (and Macro in Sheet2 Module)of my example File?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;color: #333333;;">WAHR</td><td style=";">Text</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=ISTEXT(<font color="Blue">B1:B3</font>)</td></tr></tbody></table></td></tr></table><br />


………………………….

...
Example 2…………….!

….

. It is going to take my Beginner’s brain some time to do justice to your efforts and understand and comment on this!

. I shall begin to do that (Possibly try to adapt it to my example) and hopefully be able to comment further sometime later!


. Thanks Again
. Alan Elston

P.s. My learning File again modified (to my current stage so far!!) : (XL2007 .xlsm)
https://app.box.com/s/biav19uhby7g4ji4t3gz
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
…………………………………………..



Last Bit again hopefully in correct Form!!

….
… Example 1…..
Let's say B1 holds a Text value and B2 a number value. Write in A1:

=ISTEXT(B1:B2)

Although you only get in the cell 1 result (True/False), you are examining 2 cells, and if in the formula bar you select the formula and press F9 you see that the result is, in fact, the array {TRUE;FALSE}. You are seeing 1 result in the worksheet cell but the formula generated an array with 2 results……..
………etc……

Code:
Sub RowColumnTrick1()
 
Dim v As Variant
Let v = Evaluate("ISTEXT(B1:B2)")
Dim vIfRow() As Variant
vIfRow() = Evaluate("IF(Row(B1:B2),ISTEXT(B1:B2))")
Dim vIfColumn() As Variant
vIfColumn() = Evaluate("IF(Column(B1:B2),ISTEXT(B1:B2))")
End Sub
….
…etc…..

...
…. Interesting example to watch in watch window! Sort of shows about as far as I had got originally understanding the Row / Column trick Stuff! (The following is in sheet 2 (and Macro in Sheet2 Module)of my example File?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;color: #333333;;">WAHR</td><td style=";">Text</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=ISTEXT(<font color="Blue">B1:B3</font>)</td></tr></tbody></table></td></tr></table><br />


………………………….

….

Example 2…………….!

...
. It is going to take my Beginner’s brain some time to do justice to your efforts and understand and comment on this!

. I shall begin to do that (Possibly try to adapt it to my example) and hopefully be able to comment further sometime later!


. Thanks Again
. Alan Elston

P.s. My learning File again modified (to my current stage so far!!) : (XL2007 .xlsm)
https://app.box.com/s/biav19uhby7g4ji4t3gz
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi again PGC

…...

….Example 2 - the evaluation of the formula does not return an array, it returns a simple value..
. ……..
…………………..
. ……….

Also in this case the same as with the VLookUp(). If you evaluate the formula in the worksheet in the formula bar with F9 you see that there is only 1 value returned from the formula, so there's no point in using the If(row() in <acronym>vba</acronym>. You'll just get the same value thrice.


Conclusion:

If the formula evaluated in the worksheet returns an array of values but in <acronym>vba</acronym> is only returning 1 value, you can use "if(row()" or "if(column()" bits to tell <acronym>vba</acronym> to get all the results.

If the formula evaluated in the worksheet returns only 1 value that's all you can get


Remark: I don't mean by this that the formulas could not return more than one value, they could if used as array formulas that return an array, or using some workaround, but not as simple native formulas inserted directly in a cell like we did in these examples.

Please comment.
.

…………….OK. I am following you. If I modify a version of my very original Table, that is to say change the formulas in the third row as follows:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Produnt</td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">Name</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;">Chocolate-europe aroma</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">4</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-right: 1px solid black;;">Chocolate-Cookies</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">0</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;">Choc</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-right: 1px solid black;;">Banana-Chocolate-Split</td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;color: #FF0000;;">10</td><td style="text-align: center;border-left: 1px solid black;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;color: #FF0000;;"></td><td style="text-align: center;;">Bana</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A3:A10,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A4,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A5,$A$16:$C$33,3,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=LEFT(<font color="Blue">A3:A10,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=LEFT(<font color="Blue">A4,4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=LEFT(<font color="Blue">A5,4</font>)</td></tr></tbody></table></td></tr></table><br />

And then follow your idea applied to my modified Formulas:

….

and if in the formula bar you select the formula and press F9 you see that the result is,…


...
Then I see the following

For VLOOKUP: 4

For LEFT: ={"Choc";"Choc";"Bana";"Lime";"Erdb";"Erdb";"Jama";"Wald"} (; instead of , as I am in German Excel)

. So it all ties up.
. I still am wondering why some formulas do not produce an array. Is it just “pot luck”?
. I would still be grateful if anyone out there can come up with a way to…..

….
Remark: I don't mean by this that the formulas could not return more than one value, they could if used as array formulas that return an array, or using some workaround, but not as simple native formulas
...
…. Workaround to get my VLOOKUP to return an array!


. If I come up with any ideas, or have any further contributions to this thread as I attempt to find a solution then I will report back!

Many Thanks again
Alan Elston
Bavaria
Germany
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
.........a";"Lime";"Erdb";"Erdb";"Jama";"Wald"} (; instead of , as I am in German Excel)
......
Oops... ignor that little bit . Unusually I think this time it may be the same in English and German Excel

Alan
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,737
. I would still be grateful if anyone out there can come up with a way to…..

…. Workaround to get my VLOOKUP to return an array!
Hi Alan

As you may know, functions like Index() and Vlookup() that seem to return only 1 value (when evaluated in the formula bar), can, in fact, return an array of values when used as an array formula that returns an array applied to a range of cells.

For ex, in post #6, if you use the VLookUp() as an array function that returns an array, like

- select B3:B5
- in the formula bar paste: =VLOOKUP(A3:A5,$A$16:$C$33,3,FALSE)
- confirm with Control-Shift-Enter

You'll see that the 3 correct results are returned. That's how VLookUp() behaves when applied as an array formula that spans more than 1 cell.

This means that although when you evaluate the VLookUp() you only see 1 result, the other results are somehow possible to be generated.


The workaround I was thinking was one published by XOR LX article here:

INDEX: Returning an array of values | EXCELXOR

where he presents a workaround for the case of these functions like Index() and Vlookup() that seem to return only 1 value (when evaluated in the formula bar), but that in fact can return an array of values when used as an array formula that returns an array applied to a range of cells.

This is, of course, very interesting as a learning experience, but I would not forget the simplest solution. Writing the formula in the cells and let excel evaluated it, like

Code:
With range
         .Formula = someformula
         .Value = .Value
End With
... simple and easy to read and understand.


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >F</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >G</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Produnt</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Name</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Chocolate-europe aroma</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Choc</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Chocolate-Cookies</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Choc</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Banana-Chocolate-Split</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">10</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Bana</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=8 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table><br>
<br>
<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >Addr</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >Formula</td></tr><tr><td colspan=2 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >[Book1]Sheet1</td></tr><tr><td rowspan=2 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center; " >B3:B5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em; " > =VLOOKUP(A3:A5,$A$16:$C$33,3,FALSE) </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;; " > This in an array formula that is returning an array. Select the WHOLE range, paste the formula into the formula bar <br> and confirm with CTRL+SHIFT+ENTER and not just ENTER</td></tr></table>
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
The workaround I was thinking was one published by XOR LX article here:

INDEX: Returning an array of values | EXCELXOR

where he presents a workaround for the case of these functions like Index() and Vlookup() that seem to return only 1 value (when evaluated in the formula bar), but that in fact can return an array of values when used as an array formula that returns an array applied to a range of cells.
Actually PGC the technique I outline there is neither for applying over a range of cells, nor is it even an array formula.

It is rather a method by which we can use a single-cell, non-array formula to generate an array of returns to be passed to some function, e.g. INDEX, VLOOKUP, which "normally" do not behave as such.

So as in one of my examples there, something like:

=MAX(VLOOKUP(T(IF(1,{"A","B","C"})),J1:K10,2,0))

which gives the maximum value in K1:K10 where the corresponding row entry in J1:J10 is the first occurrence in that range of either "A", "B" or "C", is a single-cell formula which resolves to:

=MAX(VLOOKUP({"A","B","C"},J1:K10,2,0))

which is e.g.:

=MAX({1,2,4})

where the VLOOKUP has been coerced into operating over an array of values.

Hope that clarifies things a bit!

Regards
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,737
Sorry I think (or, in this case, I know :) )I did not make myself clear.

Your workaround is not needed if you apply the formula over a range of cells.
Like in the example I posted in post #8, if we apply the formula over a range of cells there's no need to use any workaround, the formula works fine directly.

Alan's problem is that he wants to use vba Evaluate() to get those "other" values that you'd get directly if the formula was used on a range of cells.
There's where your workaround comes handy.
Using your workaround you can get from a formula in a single cell all the results that you'd get using the formula directly over a range of cells.

This means that this way we can use it in Evaluate() to get all the results and that solves Alan's problem.

For my example in post #8, using your workaround in the VLookUp() formula that I used in cells B3:B5, we can get all the results in the Evaluate().

Hope it's clearer now.
 

Forum statistics

Threads
1,078,516
Messages
5,340,879
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top