Array question

[AD108]

I have a project where I will have incoming order workbooks. The order
workbooks have UPC numbers and amounts.

I want to write a script that will transfer the amounts from the "order
workbook" to the "master workbook" by matching UPC numbers. The actual
structure of the books is subject to change, so it cannot be a copy and
paste.

My first thought was to transfer the UPC range and the "amounts" range into
arrays, then find each element of UPC range in the master book, and print
the amount from the corresponding element in the "amounts range". Not sure
if this would be the best approach.

Any help would be appreciated. Thanks

 

[Tom Ogilvy]

Is there a reason not to use something like vlookup?

could the incoming workbooks have UPC's not in the master workbook.

In any event, you approach of putting them into arrays should work.

Not sure what you mean by help - what kind of help are you looking for.

vArr = activesheet.Range("A1").currentRegion.Value

for i = 2 to Ubound(vArr,1)
res =
application.Match(varr(i,1),Workbooks("Master.xls").Worksheets(1).Columns(1)
if not iserror(res) then
with Workbooks("Master.xls").Worksheets(1)
.Cells(res,3) = varr(i,4)
.Cells(res,5) = varr(i,2)
end with
end if
Next

 

[AD108]

Thanks very much,

I can work with this.
Vlookup, at least hardcoded into the sheets, won't work due to cells being
cleared after certain events.