Hi pgc ( and XOR LX),
I am very grateful for you both for your further inputs.
Originally Posted by
pgc01
………..
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.
. I agree this may be a satisfactory solution and so the Codes from you and Jerry are very helpful.
. In addition I have now a third alternative from pgc post #8, using a workaround in the VLookUp() formula that was used in cells B3:B5
Code:
Sub Test3cc_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($A$3:$A$10,$A$16:$C$33,3,FALSE)"
' .Value = .Value
End With
End Sub 'Test3cc_pgc()
. I also think that I may now be going way above my inexperienced beginner’s brain now with trying to use vba Evaluate() to get all values that you'd get directly if the formula was used on a range of cells.
. Although it is somewhat above my current grasp of VBA I think I have been able to follow your arguments. Possibly naively I was hoping to rap up this very useful learning exercise I could modify my code for putting in the values in C column which only gave the one value:
Code:
Sub Evaluate_VLOOKUP()
Dim rngName As Range
Set rngName = ThisWorkbook.Worksheets("Sheet1").Range("A3:A10")
Dim rngCC As Range
Set rngCC = ThisWorkbook.Worksheets("sheet1").Range("C3:C10")
Let rngCC = Evaluate("if(row(3:10),VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE) )")
End Sub 'Evaluate_VLOOKUP()
..to something of the following from ….
Code:
Sub Evaluate_VLOOKUPpgcXORLX()
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 = Evaluate("if(row(3:10),{=VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE)} )")
Let rngEE = Evaluate("{=VLOOKUP(" & rngName.Address & ",$A$16:$C$33,3,FALSE)} ")
End Sub 'Evaluate_VLOOKUPpgcXORLX()
to put the correct values in the E column.
. As seen below this did not work. Obviously I have stretched my understanding possibly a bit too far here.
 A  B  C  D  E 

1  Produnt     
2  Name     
3  Chocolateeurope aroma  4  4  4  #### 
4  ChocolateCookies  0  4  0  #### 
5  BananaChocolateSplit  10  4  10  #### 
6  LimetteKäsekuchen  16  4  16  #### 
7  ErdbeereQuark  8  4  8  #### 
8  ErdbeereMix  0  4  0  #### 
9  Jamaica Sun  6  4  6  #### 
10  Waldbeeren  0  4  0  #### 
Sheet1
Worksheet FormulasCell  Formula 

B3  =VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE) 

B4  =VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE) 

B5  =VLOOKUP(A3:A10,$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) 


Array FormulasCell  Formula 

D3:D10  {=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)} 

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself 
. It would be great If either of you could correct this code to give the correct results. Although maybe I am naively missing the point and that is not possible. Sorry if that is the case. Never the less I have learnt a lot from participating in this thread. And I am very grateful to your inputs.
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?
Like this thread? Share it with others