Results 1 to 5 of 5

Thread: Word macro, Excel worksheet function VLOOKUP can not get to work

  1. #1
    Junior Member
    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0

    Word macro, Excel worksheet function VLOOKUP can not get to work

    Hi,
    I am using Word 2007 and Excel 2007. I writing a macro for a Word Template . I want to get data from an Excel workbook. In the macro in a With appExcel.ActiveSheet End With command, I have the two statements: BCellValue$ = .Range("B25").Value and ResponseB = MsgBox(BCellValue$, 0). They work fine and the message box displays the value in cell B25 on worksheet "Sheet1" of the workbook. In the macro following the foregoing statements I have the two statements: HCellValue$ = .WorksheetFunction.VLOOKUP(26,A2:A50,H2:H50) and ResponseH = MsgBox(HCellValue$, 0). When I run the macro I get the compile error message "expected: list seperator or )" and the colon between A2 and A50 is highlighted.

    When I have in a cell in the worksheet the formula =VLOOKUP(26,A2:A50,H2:H50), it works fine.

    Any thoughts anyone might have would be Greatly Appreciated.

    Chuck S48
    Florida USA

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Chuch S48,

    Welcome to ExcelFox !!

    The syntax for VLOOKUP seems to be incorrect. Try LOOKUP.

    If it doesn't work, attach the workbook so that we can have a detailed look.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    try

    Code:
    .Parent.Parent.WorksheetFunction.LOOKUP(26,.Range("A2:A50"),.Range("H2:H50"))
    And as Admin said, you might have intended to use LOOKUP, and if you intend to use VLOOKUP, then the arguments passed are anyway incorrect.
    Last edited by Excel Fox; 08-12-2012 at 11:20 PM. Reason: Missed to add another parent
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Thank you very much for your replies. I am busy until Monday late afternoon. I will work on my macro then and then reply. I am anxious to work on this. Thanks again. Chuck

  5. #5
    Junior Member
    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Hi Excel Fox,

    Thank you for your suggestion. First, in my initial post I used VLOOKUP. I ment to say LOOKUP. I was trying to get either to work and typed VLOOKUP with the LOOKUP syntax. I had tried LOOKUP with the LOOKUP syntax and could not get it to work. Your suggestions to put .Parent.Parent before the .WorksheetFunction, to use .Range("A2:A50") instead of A2:A50 and to use .Range("H2:H50") instead of H2:H50 worked perfectly. I had tried the last two of your suggestions without success. The .Parent.Parent suggestion was the one that freed me from my several hours of unsuccessfully trying to debug my code. I do not understand why it was necessary. I have looked at my Word Visual Basic Book and my Excel Programming Book and cannot find any information about it. If you have the time I would appreciate being enlightened. Thank you Very Much. You are providing a Great Service to amateur macro writers like me. With Best Regards, Chuck

Similar Threads

  1. Reverse Vlookup Using Choose Function
    By Excel Fox in forum Excel Help
    Replies: 8
    Last Post: 07-04-2013, 01:50 AM
  2. Replies: 1
    Last Post: 02-15-2013, 03:35 PM
  3. VLookup Any Matching Word From A Sentence
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 12-30-2012, 11:55 AM
  4. Replies: 1
    Last Post: 10-16-2012, 01:53 PM
  5. How to Get Comment by Vlookup Function :
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 10-13-2011, 05:55 PM

Tags for this Thread

Posting Permissions

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