Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
option buttons run Click code when value is changed via VBA code | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |