VBA for Vlookup is not working

[abalakrishnan2010]

Hi,

I am new to VBA programming, please help me to resolve the below question.

I have attached the excel file.

Form the attached excel, if I select the d1 cell, after run the below code it should give the result as "Feb16\", but I got an error
" Run-time error '1004': Unable to get the VLookup property of the Worksheetfuntion class ",

But if I select the "d2" cell, I got the result as "Apr16\".
I don't know why this error is coming.

The below is my code:

Sub OpenCatia()
Dim order1 As String

order1 = ActiveCell.Offset(0, -1).Value
Month = Application.WorksheetFunction.VLookup(order1, Workbooks("Open Files.xlsm").Worksheets("Sheet1").Range("a2:b20").Value, 2, False)

Thanks
Arun

 

[NARAYANK991]

Hi Arun ,

1. What is it you want to do ?

First explain your requirement before you ask anyone to debug your code ; unless someone else understands what the code is expected to do , how can the error be pinpointed ?

There are syntax errors and there are semantic errors ; syntax errors are because the code cannot be understood by the software ; eliminating such errors is the first step , but eliminating every syntax error is no guarantee that the syntax error-free code will do what you want it to do.

Semantic errors are when you expect the code to do something , and it either does nothing or it does something else.

Only when you explain what you want to do can someone see whether the code has any semantic error or not.

2. Your code uses a workbook called Open Files.xlsm which is not available in this thread ; it is possible that the error is because the value being looked up is not to be found in this workbook.

3. I am not sure whether the second line of code should use the .Value property ; try using the following line of code ( with the .Value removed ) , and see whether the error is eliminated :

Month1 = Application.WorksheetFunction.VLookup(order1, Workbooks("Open Files.xlsm").Worksheets("Sheet1").Range("a2:b20").Value, 2, False)

I have used Month1 instead of Month since Month is a VBA keyword ; when using variables within your code , never use VBA keywords as their names.

Narayan