I have written the following code to lookup and match items in closed workbook and grab specific columns from the closed wworkbook
Code: Select all
Const cols As String = "13,14,2,3,4"
Sub Test()
Dim a, x, ws As Worksheet, pt As String, r As Long, j As Long
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Worksheets("Sheet1")
a = Split(cols, ",")
Rem Use This If You Need To Let The User Select The File
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select The Data File"
.AllowMultiSelect = False
.InitialFileName = "E:\1\New Folder\To be rec\"
If .Show <> -1 Then Exit Sub
pt = .SelectedItems(1)
End With
With GetObject(pt)
For r = 2 To ws.Cells(Rows.Count, 2).End(xlUp).Row
With .Sheets(1)
x = Application.Match(ws.Cells(r, 2).Value, .Columns(1), 0)
If Not IsError(x) Then
ReDim w(UBound(a))
For j = LBound(w) To UBound(w)
w(j) = .Cells(x, Val(a(j))).Value
Next j
ws.Range("C" & r).Resize(1, UBound(a) + 1).Value = w
End If
End With
Next r
.Close 0
End With
Application.ScreenUpdating = True
MsgBox "Done...", 64
End Sub
Is there any ideas to improve the speed of such code?