Running Sum?

[RichieK]

W/O 10 UPL 330


Product Pass 1 Pass 2 Pass 3 Pass 4 Total W/O's
10 10 10 10 10 4
20 10 10 10 10 4
30 10 10 10 10 4
40 10 10 10 10 4
50 10 10 10 10 4
60 10 10 10 10 4
70 10 1
80 10 10 10 10 4
90 10 10 2
100 10 10 10 10 4
100 90 80 80


Hi all
I need some help with a spreadsheet.
I have data arranged in columns that will typically be values of ten. Based
on some other criteria the cell will or will not contain the value ten after
successive passes. What I want to do is tally up to a predetermined value
(UPL) and have an operator tell at a glance (maybe colored cells or
something) where to stop. So in the example included, the operator would
pull all work orders for the first three columns since the total is 260. I
would like to have a visual indicator that tell them to then work down the
Pass 4 column until 330 is reached – in this case the final three units of
ten would not need to be pulled.
The next day it may be all of the first three columns and the first three of
the fourth column. Any ideas for a quick fix on this?

 

[Mike H.]

Run this macro and you'll get your desired result. You may need to adjust
the locations.

Sub GetUPL()
Dim UPL As Double
Dim CumIt As Double
Dim Found As Integer
Cells.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Let UPL = Cells(2, 5).Value 'this is the value you wish to work up to

Let X = 4 'this is the first row of your 10 rows of data
For Y = 2 To 5
For X = 4 To 13
Let CumIt = CumIt + Cells(X, Y)
If CumIt >= UPL Then
Found = 1
Exit For
End If
Next
If Found = 1 Then Exit For
Next

If Found = 1 Then
Cells(X, 5).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If

End Sub

 

[RichieK]

Mike
First of – thanks a lot!
I really have to get better at the VBA part of thing so life can be easier!
Overall the your advice provided a solid solution. Some of the property
settings (TintAndShade, PatternTintAndShade) do not work – but I assume those
are for a different version of Excel – I am using Excel 2003.
If I may ask a two part follow-up question I have included the macro as it
is today below.
1. How do I change the font of the “left-over†cells to white or light gray
to make it less possible for operators to pull work orders for those part
numbers.
2. Also is there a method to make the “Regentâ€(below) name a variable so
that font is set to bold based on the current worksheet?
Worksheets("Regent").Cells(x, y).Font.Bold = True

Thank you
Rich k



Sub WO_List()
'
'
'Run this macro and you'll get your desired result. You may need to adjust
'the locations.


Dim UPL As Double
Dim CumIt As Double
Dim Found As Integer
Range("u6:aa16").Select
With Selection.Interior
.Pattern = xlNone
Selection.Font.Bold = False
' .TintAndShade = 0
' .PatternTintAndShade = 0
End

Let CumIt = 0
Let UPL = Cells(2, 25).Value 'this is the value you wish to work up to

Let x = 7 'this is the first row of your 10 rows of data
For y = 23 To 27
For x = 7 To 16
CumIt = CumIt + Cells(x, y)
If CumIt >= UPL Then
Found = 1
Exit For
End If
Next
If Found = 1 Then Exit For
Next

If Found = 1 Then
Cells(x, y).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
' .Font = Bold
' .TintAndShade = 0
' .PatternTintAndShade = 0
End With

Worksheets("Regent").Cells(x, y).Font.Bold = True
End If


End Sub