help with code
 

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

help with code
 

Where are you getting the overflow error?
This usually happens when your variable type doesn't match a value assigned
to it
For example assigning a number greater than 32,767 to an integer.


"ajcharbs" wrote in
message ...

this is what i have so far, it's labeled pretty well, if you have any
questions about whats going on let me know, and try and help me with
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 And
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 Sub


--
ajcharbs
------------------------------------------------------------------------
ajcharbs's Profile:
http://www.excelforum.com/member.php...o&userid=24457
View this thread: http://www.excelforum.com/showthread...hreadid=380545