View original (French)

Vlookup in another Excel file

Solved
Kuartz Posted messages 852 Registration date   Status Member Last intervention   -  
f894009 Posted messages 17417 Registration date   Status Member Last intervention   -
Hello,

Here is my macro: 

Sub CONTACT() Dim i As Long Dim DL As Long DL = ThisWorkbook.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To DL ThisWorkbook.ActiveSheet.Cells(i, 14) = Application.VLookup(ThisWorkbook.ActiveSheet.Cells(i, 1).Value, Workbooks("K:\AFFRETEMENT EN COURS\2015\SEPTEMBRE 2015\affretement SEPTEMBRE 2015.xls").Sheets(1).Range("T:W"), 1, False) Next i


End Sub

Excel tells me: "The index does not belong to the selection" and highlights: 

ThisWorkbook.ActiveSheet.Cells(i, 14) = Application.VLookup(ThisWorkbook.ActiveSheet.Cells(i, 1).Value, Workbooks("K:\AFFRETEMENT EN COURS\2015\SEPTEMBRE 2015\affretement SEPTEMBRE 2015.xls").Sheets(1).Range("T:W"), 1, False)


Any idea?

Thank you in advance.

Best regards.

1 answer

  1.
    f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    If file opened: 

    ThisWorkbook.ActiveSheet.Cells(i, 14) = Application.VLookup(ThisWorkbook.ActiveSheet.Cells(i, 1).Value, Workbooks("affretement SEPTEMBRE 2015.xls").Sheets(1).Range("T:W"), 1, False)


    If not opened, open it and rule no. 1, it's the simplest.
    4
    1. Kuartz Posted messages 852 Registration date   Status Member Last intervention   65
       
      Thank you, I no longer have the error message.

      However, I only get #NA every time I use VLOOKUP, it's the same...
      0
    2. Kuartz Posted messages 852 Registration date   Status Member Last intervention   65 > Kuartz Posted messages 852 Registration date   Status Member Last intervention  
       
      If I copy/paste data from one table to another, I always get NA, how does it not find the data?
      0