hi ppl...

i was wondering if anyone can help me with this code... the code was
written to compute the total qty ... the sum is calculated on the basis
of two criteria .. part no and month... the data for these two are
entered in one sheet called the 'PO Order' and the data to be displayed
is in the sheet 'Plan'.. the problem i am facing is this .. i want that
when i enter the part no and the month the sum of the total qty should
be diaplayed ... right now i am just getting the individual qty
corresponding to the part no ... in the sheet'PO Order' there can be
multiple entries for the same part no.... this is the code that i have
... what changes do u think i have to make to get the results that i
need..

Dim lastPORow As Integer
Dim lastPlanRow
Dim x As Integer
Dim pNo As String
Dim monthNo As Integer
Dim totalQty As Long

ThisWorkbook.Activate
Sheets("PO Order").Select
Range("A2").Select
Selection.End(xlDown).Select
lastPORow = Selection.Row

'Sort the PO Order sheet by PartNumber to keep all the entries for
a part number together
' Selection.End(xlUp).Select
' Range(Selection, Selection.End(xlToRight)).Select
' Range(Selection, Selection.End(xlDown)).Select
' Selection.Sort Key1:=Range("A3"), Order1:=xlAscending,
Key2:=Range("D3") _
' , Order2:=xlAscending, Key3:=Range("C3"), Order3:=xlAscending,
Header:= _
' xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
' DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
' xlSortNormal
' Range("A3").Select


' Trim leading and trailing spaces from the part number, if any
For x = 3 To lastPORow
pNo = Trim(Range("A" & x))
Range("A" & x) = pNo
Next x

Sheets("Plan").Select
Range("B4").Select
Selection.End(xlDown).Select
lastPlanRow = Selection.Row
If lastPlanRow 65530 Then
Selection.End(xlUp).Select
Range("B4").Select
End
End If
For x = 4 To lastPlanRow
totalQty = 0
monthNo = getMonthNumber(Range("A" & x))
pNo = Range("B" & x)
For y = 2 To lastPORow
If UCase(Sheets("PO Order").Range("A" & y)) = UCase(pNo)
And Sheets("PO Order").Range("E" & x) = monthNo Then
totalQty = totalQty + Sheets("PO Order").Range("C" & x)
End If
Next y
Range("D" & x) = totalQty
Next x
End Sub

why not use an array formula?

for e.g. in Plan!A2, PNo123456
and in PlanB2, 01/01/06

{=SUM(('PO Order'!A2:A14=Plan!A2)*('PO Order'!B2:B14=Plan!B2)*1)}


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=564303