Word macro, Excel worksheet function LOOKUP won't 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.LOOKUP(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 =LOOKUP(26,A2:A50,H2:H50), it works fine.

Any thoughts anyone might have would be Greatly Appreciated.

Chuck S48
Florida USA

[BigBas]

I think the problem is that you are using standard Excel syntax in VBA code. Try changing the code to

Please Login or Register  to view this content.

Let me know if it works

[Chuck S48]

Hi BigBas,

Thank you for your reply. I tried your suggestion and got the error message "Run-time error '1004': Method 'Range' of object '_Global' failed".
It seems like maybe your suggestion will work with some additional tweeking. Any supplement to your suggestion will be greatly appreciated.

Chuck

[BigBas]

The problem is likely within the LOOKUP function. I dont know your data specifically, but the LOOKUP function expects that the lookup range is in ascending order. Meaning if your data in Range("A2:A50") is not in ascending order, it will err.

We have a few options. One option is to go with a VLOOKUP instead of traditional lookup. Another option is to have a line of code auto sort your data prior to running the LOOKUP.

Does this make sense? Do you need assistance with either?

[Chuck S48]

Hi BigBass,
Thank you for your most recent reply.
My data in the Range("A2:A50") is in ascending order although it skips some numbers.

I have tried the following statements without success and received the following error messages:

HCellValue$ = .WorksheetFunction.VLOOKUP(26, A2:AE26, 8, FALSE)
Error message: Compile error: Expected: list separator or )

HCellValue$ = .WorksheetFunction.VLookup(26, Range("A2", "AE26"), 8, False)
Error Message: Compile error: Can't find project or library

HCellValue$ = .WorksheetFunction.VLookup(26, .Range("A2", "AE26"), 8, False)
Error Message: Run-time error '438': Object does not support this property or method

HCellValue$ = .WorksheetFunction.VLookup(26, ActiveSheet.Range("A2", "AE26"), 8, False)
Error Message: Run-time error '91': Object variable or With block not set

Do you have any further suggestions? Would you like me to send you the entire macro to look at?

Thank you Very Much for your help.

With Best Regards,

Chuck

[Chuck S48]

Hi Again BigBas,
FYI, I added the statement appExcel.ActiveSheet.Range("A26:B27").Select after the With, End With block and it worked fine.
Chuck

[Chuck S48]

Hi Again BigBas,

I added near the beginning the statement Dim MyRange AS Range. I also added before the With, End With block a statement MyRange = appExcel.ActiveSheet.Range("A20:Z26"). When I ran the macro I got the error message: Compile error: Can't find project or library. I have a reference in the macro to the Microsoft Excel 12.0 Object Library. The ponr of this isthat for some reason I can't refer to a Range that is other than a single cell. Any thoughts you might have will be Greatly Appreciated.
With Best Regards,

Chuck