this is what i have so far, it's labeled pretty well, if you have an
questions about whats going on let me know, and try and help me wit
this overflow error....thanks
ub Update()
Dim i As Long, LastRow As Long, PO As String
Dim CustDate As Date, CustDate2 As Date
Dim POShipped As Range, firstaddress As String
Dim QOrdered As Integer, QShipped As Integer
Dim QOD As Integer, QTotalShipped As Integer
Dim ShippedRow As Long
QTotalShipped = 0
Application.Calculation = xlCalculationManual
'finds last used row in column B
LastRow = Sheets("CRJOEM").Range("B65536").End(xlUp).Row
'will loop through each cell in column B starting from B9
For i = 9 To LastRow
With Sheets("CRJOEM")
'determine PO# and date
PO = .Cells(i, "B")
CustDate = .Cells(i, "I").Value
QOrdered = .Cells(i, "L").Value
'find PO# on Shipped sheet
With Sheets("Shipped")
Set POShipped = .Cells.Find(PO, LookIn:=xlValues)
'if PO# is found, code should enter Do...Loop
If Not POShipped Is Nothing Then
firstaddress = POShipped.Address
Do
ShippedRow = POShipped.Row
'get date for PO
CustDate2 = .Cells(ShippedRow, "L").Value
'check dates
If CustDate = CustDate2 Then
QShipped = .Cells(ShippedRow, "Q").Value
QTotalShipped = QShipped + QTotalShipped
End If
'since orderes can ship in sections...
If QTotalShipped < QOrdered Then
'look for another instance of PO#
Set POShipped = .Cells.FindNext(POShipped)
End If
Loop While Not POShipped Is Nothing An
POShipped.Address < firstaddress
QOD = QOrdered - QTotalShipped
End If
End With
.Cells(i, "N").Value = QOD
End With
Next i
MsgBox ("Done.")
Application.Calculation = xlCalculationAutomatic
End Su
--
ajcharb
-----------------------------------------------------------------------
ajcharbs's Profile:
http://www.excelforum.com/member.php...fo&userid=2445
View this thread:
http://www.excelforum.com/showthread.php?threadid=38054