PDA

View Full Version : Compare between two lists project



YellowLabPro
12-15-2006, 09:58 AM
I am interested in comparing two lists. There will be two criteria's to consider- 1) if the control list, list (A) does not contain an item that exists in list (B), then copy this item to a third list, list (C). If the item exists in both lists and is greater than zero (0) then copy that item to the third list, list (C).

My question is can VBA do this, if so where and how would I start this?
This project is for my online store. Our inventory contantly changes and I need to update, add and delete item records daily. Currently I just do a mass replacement, but w/ over 10,000 records, it takes about 2 hours to do all the checking and replacing.

Thanks,

YLP

Bob Phillips
12-15-2006, 10:12 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim J As Long

Application.ScreenUpdating = False

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If Application.CountIf(.Columns(1), .Cells(i, "A")) = 0 Or _
(Application.CountIf(.Columns(1), .Cells(i, "A")) > 0 And _
.Cells(i, "A").Value > 0) Then
J = J + 1
.Cells(J, "C").Value = .Cells(i, "A").Value
End If
Next i

End With

Application.ScreenUpdating = True

End Sub