Snasui Excel Forum

Code: Select all

Sub mylookupPR()
    Dim lastrow As Long
    lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    Set myrange = Workbooks("G\B\tan\data2.xls").Sheets1.Range("A:D")
   '...Other code
On Error Resume Next
For i = 1 To lastrow
    Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 1), myrange, 13, False)
    Cells(i, 6).Value = Cells(i, 6).Value
    Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 1), myrange, 14, False)
    Cells(i, 7).Value = Cells(i, 7).Value
Next i
End Sub

Code: Select all

Sub mylookupPR()
    Dim lastrow As Long
    lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    Set myrange = Workbooks("data2.xls").Sheets("Sheet1").Range("A:N")
On Error Resume Next
For i = 4 To lastrow
    Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 1), myrange, 14, False)
    'Cells(i, 6).Value = Cells(i, 6).Value
    Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 1), myrange, 13, False)
    'Cells(i, 7).Value = Cells(i, 7).Value
Next i
End Sub

Code: Select all

Sub mylookupPR()
Dim sFilename As String
Dim myrange, myValue
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 4 To lastrow
sFilename = "G\B\tan\data2.xls"
With GetObject(sFilename)
    myrange = .Sheets("Sheet1").Range("A:N")
   Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 1), myrange, 14, False)
   Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 1), myrange, 13, False)
    .Close
End With
Next i
Application.ScreenUpdating = True
End Sub

Code: Select all

Dim lastrow As Long, wb As Workbook
Dim myrange As Range, tb As Workbook
Set tb = ThisWorkbook
With tb.Sheets(1)
    lastrow = .Range("a" & .Rows.Count).End(xlUp).Row
End With
Set wb = Workbooks.Open("G\B\tan\data2.xls")
Set myrange = wb.Sheets(1).Range("a:n")
On Error Resume Next
For i = 4 To lastrow
    With tb.Sheets(1)
        .Cells(i, 6) = Application.WorksheetFunction.VLookup( _
            .Cells(i, 1), myrange, 13, False)
        .Cells(i, 7) = Application.WorksheetFunction.VLookup( _
            .Cells(i, 1), myrange, 14, False)
    End With
Next i
wb.Close False