Sunday, June 3, 2018
VBA Excel Matching value's in sheets and copy rows
Answer
Im pretty new into programming with VBA in excel I got my code working but its just too slow.
Could you guys help me speeding up my task.
Sheet2 has around 42.000 items and sheet1 varies from 100 to 1000
Basicly I look for a value in 2 sheets when there is a match I copy the info into sheet1 from sheet2.
See my code below.
Sub CheckAML()
Dim i As Long
Dim j As Long
Sheet1LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheet2LastRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For j = 1 To Sheet1LastRow
For i = 1 To Sheet2LastRow
If Worksheets("Sheet1").Cells(j, 1).Value = Worksheets("Sheet2").Cells(i, 1).Value Then
Worksheets("Sheet1").Cells(j, 3).Value = Worksheets("Sheet2").Cells(i, 2).Value
Worksheets("Sheet1").Cells(j, 4).Value = Worksheets("Sheet2").Cells(i, 3).Value
Worksheets("Sheet1").Cells(j, 5).Value = Worksheets("Sheet2").Cells(i, 4).Value
Else
End If
Next i
Next j
Application.ScreenUpdating = True
End Sub
Would also be nice if Sheet2 could be a seperate workbook.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment