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

[Chuck S48]

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

[Admin]

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.

[Excel Fox]

try

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

[Chuck S48]

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

[Chuck S48]

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