find the matching and supply the quantity

[Ren]

hi,anyone can help me pl

sheet1(polist)
Number item reqd qty store1 store2 FAB ETA
45594010 Apple 170092
45600810 Apple 331068
45613110 orange 294954

sheet2(slrs)
item store1 store2 total bal allocation qty
Apple 339173 339173
orange 20 3000 3020

sheet3(fab)
item ETA Total bal allocation qty
Apple 20071224 113703
20080104 1279155
orange 20080101 159942
20080104 90876
20080119 272629

after the allocation,the sheets will look like this

sheet1(polist)
Number item reqd qty store1 store2 FAB ETA
45594010 Apple 170092 170092
45600810 Apple 331068 169081 113703 20071224
48285 20080104

45613110 orange 294954 20 3000 159942 20080101
90876 20080104
41136 20080119


sheet3(fab)
item ETA Total bal allocation qty
Apple 20071224 113703 0 45600810 113703
20080104 1279155 1230870 45600810 48285
orange 20080101 159942 0 45613110 159942
20080104 90876 0 45613110 90876
20080119 272629 231493 45613110 41136

I have 3 sheets (pl see at the end)
Sheet1 “LIST†(n number of rows,keeps changing)
Sheet2 “slrsâ€
Sheet3 “fabâ€

Sheet1 “LISTâ€
column A…..Number
column B…..ITEM
column C…..Required Quantity
column D…..store1(place the value here from store1 of SLRS)
column E…..store2 (place the value here from store2 of SLRS)
column F…..FAB
column G…..ETA

sheet2 “SLRSâ€
column A…..ITEM
column B…..store1
column C…..store2
column D…..total
column E…..balance
column F…..allocation
column G…..quantity

sheet3 “FABâ€
column A…..ITEM
column B…..ETA (it’s nothing but the date of arrival)
column C…..Total
column D…..balance
column E…..allocation
column F…. quantity


take the item in clm B of “POLIST†and find whether we have
the matching in “slrs†sheet

if there is a matching then take the corresponding value and place it
in "LIST" column D(store1) or E(store2)( based on the slrs-location ie
store1 or store2),check the quantity is sufficient enough to supply the
columnC ie required quantity,
If not goto “Fab†and search for the matching device,if u find the
matching take the value along with the ETA
If there is no matching in both sheets,then leave it empty
if qty is not enough write NA
In both the cases we have to take the corresponding NUMBER and place
it in allocation column.
The quantity you supplied in quantity column,and the balance quantity left.
And repeat the process for all the devices.

The items will appear many no of times.

Note: some items have the matching more than one ie if u don’t find
apple
or the sufficient qty is not there then look for pineapple

the rows more 150 to 200,i need to match for all these rows with multiple
number of rows in sheet2 and sheet3 and it changes everyday.it takes more
than one day to do this work for me.

any help highly appreciated.i am very nocice to this VBA macro

Ren

 

[Ren]

this is the code i got and it will compare sheet1 and sheet2,
but i want the control to go through sheet1 and then to sheet2.
and the item apple will appear many items,so when the item appears second
time,it should automatically to the next row to enter the quantity.

Sub allocation()
With Sheets("polist")
Sh1LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
Set Sh1Range = .Range("B2:B" & Sh1LastRow)
End With
With Sheets("slrs")
Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2Range = .Range("A2:A" & Sh2LastRow)
End With
With Sheets("FAB")
Sh3LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh3Range = .Range("A2:A" & Sh3LastRow)
End With

For Each sh1cell In Sh1Range
Set c = Sh2Range.Find( _
what:=sh1cell, LookIn:=xlValues)

If c Is Nothing Then

sh1cell.Interior.ColorIndex = 4
sh1cell.Offset(0, 1).Interior.ColorIndex = 4
Else

If sh1cell.Offset(0, 1) < c.Offset(0, 3) Then
sh1cell.Offset(0, 2).Value = sh1cell.Offset(0, 1)
c.Offset(0, 6).Value = sh1cell.Offset(0, 1)
c.Offset(0, 4).FormulaR1C1 = "=RC[-1]-RC[2]"
c.Offset(0, 5).Value = sh1cell.Offset(0, -1)
Else
If sh1cell.Offset(0, 2) > c.Offset(0, 3) Then
sh1cell.Offset(0, 2).Value = c.Offset(0, 3)
c.Offset(0, 6).Value = sh1cell.Offset(0, 1)
c.Offset(0, 4).FormulaR1C1 = "=RC[-1]-RC[2]"
c.Offset(0, 5).Value = sh1cell.Offset(0, -1)

End If
End If
End If
Next sh1cell
End Sub