Help writing loop

[YellowLabPro]

I have two projects that I need loops for( This is an area of mystery to me still....:dunno), at least I think this will be the approach. A lookup may come into play, I could use some advice on the approach.

I have four sheets that are involved in the equation.
Inventory1.xls (Current inventory)
Inventory2.xls (Online inventory)
InventoryNew.xls (records added to Current inventory)
Upload.xls (records that will be uploaded back to the Onlin inventory)


The reason for this is, Inventory1.xls and Inventory2.xls are big, 15,000 rows by 30 columns of data. InventoryNew.xls is only 5-50 records of data.

Scenario 1
I want to locate the records in Inventory1.xls that I just added from InventoryNew.xls, and copy the entire row of these new records from Inventory1.xls to a 4th sheet- Upload.xls. The reason for this is that once the records are imported into Inventory1.xls they get a great deal more detail added to the records and this needs to be copied to the Upload.xls worksheet. I think this would be easier than trying to do a Vlookup for the 28 additional columns of data.
For Each,
If x=y then
copy y to Upload.xls("B2")

Scenario 2
Compare Inventory1.xls to Inventory2.xls records totals, Column B is item record# and Column E is Qty. Whichever records are different, +/- then copy these records to Upload.xls.

I will do the leg work, but I need help in getting started....

Thanks for taking the time to read...

YLP

[Bob Phillips]

Doug,

The rationale behind locating the records seem upside down to me. If you have just copied them from New, why not add extra logica at that copy point to copy them over to Upload.xls, it will be far more efficient than looping through again.

[YellowLabPro]

Bob,
Yes you are correct, I went back and looked it over and I can do it that way. All the information is there, I just need to add some other code and it will work just fine.

How about scenario 2, care to comment or suggest how I can achieve a solution for this one?

Thanks for the help

YLP

[Bob Phillips]

Doug, Comparing two lists using a matching process is quite tricky (I personally find it quite straightforward, but I was brought on batch processing where that sort of thing was the norm). An easier way in an Excel/VBA environment IMO is to rfun through one list and use Application.Match against the other list on the key values, writing off those not found. Then repeat on the other list.

[YellowLabPro]

Thanks for the help...
I finished the first requirement today....
Now onto the comparing-- I will do some reading on it. If you can find some time to provide a small demo of the Application.Match?
All of the values will be in each list, it is more to match the quantites and adjust list#2 from list#1.
Make sense?

thanks again for the assistance

YLP

[Bob Phillips]

It is quite straight-forward



With Worksheets("Sheet1")
For i = 1 To LastRow
If Not IsError(Application.Match(.Cells(i,"A").Value, Worksheets("Sheet2").Columns(1),0) Then
'copy row i details off
End If
Next i
End With