FredMFoley
Board Regular
- Joined
- Mar 18, 2002
- Messages
- 58
I would appreciate any help to speed this sub up please as my knowledge of VBA is still very limited.
Column A on Sheet1 contains about 1500 unique values (no blanks).
Column A on Sheet2 contains about 1000 of the Sheet1 values (no blanks).
Neither column is sorted and both sheets have data in other columns.
My sub finds the matching values in the two A Columns, then transfers data IN OTHER COLUMNS from one sheet to the other, and puts it in the matching row.
While I'm pleased that it works, it is frustratingly slow (about 80sec), and I'm sure there would be much faster ways to do it.
Sub CompareAndTransfer()
Dim i%, j%, OldCodes%, NewCodes%, NewColumn%, NewSheet$, TextFile$, Codes$
For i = 1 To OldCodes '2 allows for heading
For j = 1 To NewCodes
If Sheets("Codes").Cells(i, 1) = Sheets(TextFile).Cells(j, 1) _
Then Sheets("Codes").Cells(i, 4) = Sheets(TextFile).Cells(j, 6) _
: j = NewCodes 'jump out when found (to save time)
Next j
Next i
End Sub
Thank You
Fred
Column A on Sheet1 contains about 1500 unique values (no blanks).
Column A on Sheet2 contains about 1000 of the Sheet1 values (no blanks).
Neither column is sorted and both sheets have data in other columns.
My sub finds the matching values in the two A Columns, then transfers data IN OTHER COLUMNS from one sheet to the other, and puts it in the matching row.
While I'm pleased that it works, it is frustratingly slow (about 80sec), and I'm sure there would be much faster ways to do it.
Sub CompareAndTransfer()
Dim i%, j%, OldCodes%, NewCodes%, NewColumn%, NewSheet$, TextFile$, Codes$
For i = 1 To OldCodes '2 allows for heading
For j = 1 To NewCodes
If Sheets("Codes").Cells(i, 1) = Sheets(TextFile).Cells(j, 1) _
Then Sheets("Codes").Cells(i, 4) = Sheets(TextFile).Cells(j, 6) _
: j = NewCodes 'jump out when found (to save time)
Next j
Next i
End Sub
Thank You
Fred