Vlookup In Vba

Hi everybody,

I have the following code which is working perfectly on retrieving data from another sheet for VLOOKUP Wroksheet Function:

Private Sub Worksheet_Activate()

On Error GoTo MyErr

For C = 2 To 20
If Cells(C, 1) <> "" Then
Cells(C, 2) = Application.WorksheetFunction.VLookup(Cells(C, 1), Sheet1.[A2:B20], 2, 0)
End If
Next
Exit Sub

MyErr:
  If Err <> 0 Then
    MsgBox "No Data Available ", vbExclamation, "Missing Data"
  Else
    Resume
  End If

End Sub

I want to develop this code to retrieve data from another workbook >> I tried the following code but it didn't work well with me >> it always gives me an error .. Can You please help me in this case??

Private Sub Worksheet_Activate()

On Error GoTo MyErr

For C = 2 To 20
If Cells(C, 1) <> "" Then
Cells(C, 2) = Application.WorksheetFunction.VLookup(Cells(C, 1), ActiveWorkbook.Path & "\Test.xls" &  Sheet1.[A2:B20], 2, 0)
End If
Next
Exit Sub

MyErr:
  If Err <> 0 Then
    MsgBox "No Data Available ", vbExclamation, "Missing Data"
  Else
    Resume
  End If

End Sub

When I activate the concern sheet >> error # 13 is appearing and the type of this error is mismatching >> and when I debug it >> the following line is highlighted

Cells(C, 2) = Application.WorksheetFunction.VLookup(Cells(C, 1), ActiveWorkbook.Path & "\Test.xls" & Sheet1.[A2:B20], 2, 0)

I think the problem with the path of the workbook Test >> in other words >> exactly with the following part:

ActiveWorkbook.Path & "\Test.xls" & Sheet1

Can you please check if this way of writing the path for a sheet in another workbook is correct or not.

Thank you again.

Hi,

Could it be that you are only missing the exclamation mark !

ActiveWorkbook.Path & "\Test.xls!" & Sheet1

HTH
Cheers
Carim

It didn't work

try this:

=VLOOKUP(B6,[Test.xls]Sheet1!$A$2:$B$20,2,False)

Regards,
Simon