Comparing records from 2 tables

  • Thread starter Thread starter Wes Zalewski
  • Start date Start date
W

Wes Zalewski

Hello Everyone.
I wasn't reading those newsgroups for a few years.
Time flies.

I hope someone can help me.

I have data extracted through ODBC to 2 sheets.
Sheet "01" contains all purchase order lines in the last two years. About
30,000 records.
Sheet "MD", (Multiple delivery), contains about 3,000 records.
All fields on 01 are in MD, but MD sheet has a few additional fields in the
middle.

I copy the whole sheet MD to 'Report' sheet, since I need all delivery
details, and have to add remaining records from 01 sheet, excluding already
existing purchase order lines on MD.
I used on 01 sheet Index / Match formula to mark in the column on the right
records that already exist on MD and then can use filter to copy and paste
remaining records but I would like Purchasing Supervisor to 'press a button'
and have this done automatically.

I have problem with two steps. One is with selecting the range
automatically, the other is Do Until loop. I have never created macro that
does looping at all.
I can see those two being really useful for us, since we have a lot of table
structured data imported through ODBC.

Could someone help, please.

Thank you in advance.
Wes
(e-mail address removed)
 
Dim rng as Range, rng1 as Range, cell as Range
Dim res as Variant
set rng = worksheets("MD").Range("A1").CurrentRegion
set rng1 = worksheets("01").Range("A1").CurrentRegion.Columns(1).Cells
for each cell in rng.columns(1).Cells
if cell.row = 1 then
' do nothing, header row
else
' process the row
res = Application.match(cell.Value,rng1,0)
if not iserror(res) then
' match found for column A values
msgbox rng1(res).Address(external:=True
else
' not found
end if
end if
Next

assume the value in column A is sufficient for a match. If not, then you
would need to use the Find command, find matches in column A (or other more
appropriate column which will narrow the possible matches, then check all
the possible for the fields that determine a match).
 
Back
Top