A B C
Order No No of Units Lots
K101 17
K102 17
K103 15
K104 23 Lot one
K105 25
K106 26
K107 28 Lot two
K108 29
K109 24
K110 26 Lot three
K111 23
K112 27
K113 25 Lot Four
K114 14
K115 5
K116 10 Lot five
Refer Example
When sum total of coloum B reaches 70 or above units,. Then mark as lot
one in coloum C.
From the next cell it should sum again to reach another 70 or above and
mark as lot 2.
It is not necessary that no of units must be exact 70. It should be
between 70 and 80 units
The logic seems intriguingly simple, but I could not figure out a formula to do
it.
On the other hand, it is pretty simple with a VBA routine. The problem there
has to do with selecting the area of interest. In the routine below, I simply
looked at the range B2:B1000 and assumed that the entries would be contiguous,
and that the first non-numeric entry would be the end of the range. I also
assumed that the Lot numbers would be in the adjacent column (column C).
Depending on your set up, you may wish to program this a bit differently.
Oh, and I don't understand the logic behind your labeling K116 with a Lot five
(above) as 14 + 15 + 10 is less than 70. So I assume it is a typo.
You state that the number of units should be between 70 and 80. I think you
mean the number of units should be equal to or greater than 70.
However, what if the series were 34 34 20? That would be greater than 80. ???
Finally, I noted that you had "spelled out" the Lot number. So I took a VBA
routine from the MSKB and modified it a bit.
To enter this, <alt><F11> will open the Visual Basic Editor. Ensure that your
project is highlighted in the Project Explorer window, then select
Insert/Module. Paste the code below into the window that opens. You may have
to delete the initial "Option Explicit" line.
To use this, <alt><F8> and select the SumTo70 Macro
==================================
Option Explicit
Sub SumTo70()
Dim c As Range, AOI As Range
Dim Last70 As Range
Dim TempSum As Integer
Dim LotCount As Integer
Set AOI = [b2:b1000]
AOI.Offset(0, 1).Clear
For Each c In AOI
If Not IsNumeric(c.Value) Then Exit For
TempSum = TempSum + c.Value
If TempSum >= 70 Then
LotCount = LotCount + 1
c.Offset(0, 1).Value = "Lot " & SpellNumber(LotCount)
TempSum = 0
End If
Next c
End Sub
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
SpellNumber = Dollars & Cents
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
===========================
--ron
2,">0"))