Similarly to another couple posters, I have a SLOW macro to compare cells in one workbook against cells in another...I mean this kludge takes 5 hours to run on a fast machine.
Could someone point out the many ways I've programmed this worng?
Sub Comparison()
Application.ScreenUpdating = False
Dim uRow, dRow As Long
Dim ItemNo, j, i, h
Dim Path, Description, Revision, SheetSize As String
Workbooks("drawinglist.xls"
.Activate
Sheets(2).Select
dRow = Cells(7000, 1).End(xlUp).Row
Workbooks("ultralist.xls"
.Activate
Sheets(2).Select
uRow = Cells(15000, 1).End(xlUp).Row
h = 1
i = 1
For x = 1 To dRow
j = h
For y = 1 To uRow
If Workbooks("drawinglist.xls"
.Sheets(2).Cells(x, 1).Value = Workbooks("ultralist.xls"
.Sheets(2).Cells(y, 1).Value Then
ItemNo = Workbooks("drawinglist.xls"
.Sheets(2).Cells(x, 1).Value
Path = Workbooks("drawinglist.xls"
.Sheets(2).Cells(x, 2).Value
Description = Workbooks("ultralist.xls"
.Sheets(2).Cells(y, 2).Value
Revision = Workbooks("ultralist.xls"
.Sheets(2).Cells(y, 3).Value
SheetSize = Workbooks("ultralist.xls"
.Sheets(2).Cells(y, 4).Value
ThisWorkbook.Sheets(1).Cells(h, 1) = ItemNo
ThisWorkbook.Sheets(1).Cells(h, 2) = Description
ThisWorkbook.Sheets(1).Cells(h, 3) = Revision
ThisWorkbook.Sheets(1).Cells(h, 4) = SheetSize
ThisWorkbook.Sheets(1).Cells(h, 5) = Path
h = h + 1
End If
Next y
If j = h Then
ItemNo = Workbooks("drawinglist.xls"
.Sheets(2).Cells(x, 1).Value
Path = Workbooks("drawinglist.xls"
.Sheets(2).Cells(x, 2).Value
Description = "NOT IN ULTRALIST"
ThisWorkbook.Sheets(2).Cells(i, 1) = ItemNo
ThisWorkbook.Sheets(2).Cells(i, 2) = Description
ThisWorkbook.Sheets(2).Cells(i, 5) = Path
i = i + 1
End If
Next x
End Sub
Thanks for the help,
Alex
Could someone point out the many ways I've programmed this worng?
Sub Comparison()
Application.ScreenUpdating = False
Dim uRow, dRow As Long
Dim ItemNo, j, i, h
Dim Path, Description, Revision, SheetSize As String
Workbooks("drawinglist.xls"
Sheets(2).Select
dRow = Cells(7000, 1).End(xlUp).Row
Workbooks("ultralist.xls"
Sheets(2).Select
uRow = Cells(15000, 1).End(xlUp).Row
h = 1
i = 1
For x = 1 To dRow
j = h
For y = 1 To uRow
If Workbooks("drawinglist.xls"
ItemNo = Workbooks("drawinglist.xls"
Path = Workbooks("drawinglist.xls"
Description = Workbooks("ultralist.xls"
Revision = Workbooks("ultralist.xls"
SheetSize = Workbooks("ultralist.xls"
ThisWorkbook.Sheets(1).Cells(h, 1) = ItemNo
ThisWorkbook.Sheets(1).Cells(h, 2) = Description
ThisWorkbook.Sheets(1).Cells(h, 3) = Revision
ThisWorkbook.Sheets(1).Cells(h, 4) = SheetSize
ThisWorkbook.Sheets(1).Cells(h, 5) = Path
h = h + 1
End If
Next y
If j = h Then
ItemNo = Workbooks("drawinglist.xls"
Path = Workbooks("drawinglist.xls"
Description = "NOT IN ULTRALIST"
ThisWorkbook.Sheets(2).Cells(i, 1) = ItemNo
ThisWorkbook.Sheets(2).Cells(i, 2) = Description
ThisWorkbook.Sheets(2).Cells(i, 5) = Path
i = i + 1
End If
Next x
End Sub
Thanks for the help,
Alex