Page 2 of 10 FirstFirst 1234 ... LastLast
Results 11 to 20 of 98

Thread: VBA Evaluate Range and VLOOKUP

  1. #11
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,707
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    P. S.

    Actually, I just tried your workaround in my example in post #8 and although it worked OK in the worksheet it seems it does not work with vba Evaluate().
    Can you confirm this?
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  2. #12
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    Quote Originally Posted by pgc01 View Post
    P. S.

    Actually, I just tried your workaround in my example in post #8 and although it worked OK in the worksheet it seems it does not work with vba Evaluate().
    Can you confirm this?
    Er, sorry. Not much of an expert when it comes to VBA.

    Can you confirm what the code is to be used?

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  3. #13
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,707
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    This is a simple example.

    In D1:

    =VLOOKUP(T(IF(1,{"B","A"})),A1:B2,2,0)

    If I select the formula in the formula bar and evaluate it (F9) I get, as expected: {5,4}

    in vba:

    Code:
    Sub TestEval()
    Dim v As Variant
    
    v = Evaluate("VLOOKUP(T(IF(1,{""B"",""A""})),A1:B2,2,0)")
    End Sub
    After the Evaluate I inspect v and get just 5 in v, not the array that I wanted.

    It doesn't mean it won't work if the VLookUp() is a parameter in a function like Max(), or Average(), but I can't get just the array of values resulting from the VLookUp() like I can when I evaluate the formula in the formula bar.


     ABCDE
    1A4 5 
    2B5   
    3     
    [Book1]Sheet7




    AddrFormula
    [Book1]Sheet7
    D1 =VLOOKUP(T(IF(1,{"B","A"})),A1:B2,2,0)
    This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  4. #14
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,827
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    You just need one of the usual tricks to force the return of an array:
    Code:
    v = Evaluate("INDEX(VLOOKUP(T(IF(1,{""B"",""A""})),A1:B2,2,0),)")
    for instance.

    Not sure I want to be maintaining this code though.

  5. #15
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    Quote Originally Posted by RoryA View Post
    You just need one of the usual tricks to force the return of an array:
    Code:
    v = Evaluate("INDEX(VLOOKUP(T(IF(1,{""B"",""A""})),A1:B2,2,0),)")
    for instance.

    Not sure I want to be maintaining this code though.
    Interesting. Wonder why it requires the extra coercion when used in VBA?

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  6. #16
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,827
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    Because otherwise it's as if you entered it into a cell normally - you only get the first value returned.

  7. #17
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    Hi pgc ( and XOR LX),

    I am very grateful for you both for your further inputs.

    Quote Originally Posted by pgc01 View Post
    ………..

    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.



    ABCDE
    1Produnt
    2Name
    3Chocolate-europe aroma444####
    4Chocolate-Cookies040####
    5Banana-Chocolate-Split10410####
    6Limette-Käsekuchen16416####
    7Erdbeere-Quark848####
    8Erdbeere-Mix040####
    9Jamaica Sun646####
    10Waldbeeren040####

    Sheet1



    Worksheet Formulas
    CellFormula
    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 Formulas
    CellFormula
    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?

  8. #18
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    Quote Originally Posted by DocAElstein View Post
    Hi pgc ( and XOR LX),

    I am very grateful for you both for your further inputs.
    P.S. Thanks also RoryA for your inputs. Sorry I allways semm to get replies a bit late out here in Germany!

  9. #19
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,707
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    Quote Originally Posted by RoryA View Post
    You just need one of the usual tricks to force the return of an array:
    Code:
    v = Evaluate("INDEX(VLOOKUP(T(IF(1,{""B"",""A""})),A1:B2,2,0),)")
    for instance.
    Thanks Rory, that's what was missing.

    Quote Originally Posted by RoryA View Post
    Not sure I want to be maintaining this code though.
    I agree, but I'm a sucker for understanding how it works, even if I don't plan on using it.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  10. #20
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,827
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Evaluate Range and VLOOKUP

    Quote Originally Posted by pgc01 View Post
    I agree, but I'm a sucker for understanding how it works, even if I don't plan on using it.
    I couldn't agree more!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •