Hi Alan

Interesting question.

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

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

I can give you my take on this.Code:rngCC = Application.WorksheetFunction.VLookup(rngName, Range("A16:C33"), 3, False)

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:

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

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.

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 FalseCode:Dim v As Variant v = Evaluate("IF(ROW(B1:B2),ISTEXT(B1:B2))")

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:

and inspect v after each Evaluate() statement you'll see that exactly.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))")

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.

## Like this thread? Share it with others