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

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.

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.

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

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