Tom,
Thanks for the response. Maybe the format I pasted in did not come out
correctly.
For a stimge item, the current OH is 6. The sales forecast for futre weeks
would be Week1 2 units. Week2 3 units. Week3 1 unit. The result I am
looking for is for VBA to run a loop so that it takes the current OH and
consecitively goes through and takes the future weeks of sales away until I
hit zero. Then count the # of weeks for this to occur. This example, I own
6. after selling 2 units it will be 4, then after the 2nd weeks when I sell
3 units, I have 1 left. The 3rd week, I would sell the last unit. Hence I
currently have 3 weeks of supply.
I have a sample of VBA for this, but I did not write it and it current tries
to pull OH from another Database, which I do not need. I will post it below:
Sub HIGH_WOS()
Dim MySize1 As Integer
Dim MyCount1 As Integer
Dim GAFS As Long
Dim TtlSales As Long
If Cells(8, 14).Value = "Total" Then MySize1 = 6
If Cells(8, 21).Value = "Total" Then MySize1 = 13
If Cells(8, 34).Value = "Total" Then MySize1 = 26
If Cells(8, 60).Value = "Total" Then MySize1 = 52
If Cells(8, 73).Value = "Total" Then MySize1 = 65
If MyCount1 = 0 Then
Cells(9, 2).Select
Do While IsNumeric(ActiveCell.Value) = True And ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop
MyCount1 = ActiveCell.Row
End If
Cells(9, 2).Select
For x = 9 To MyCount1 - 1
If Cells(x, MySize1 + 11).Value > 20 And Cells(x, 7).Value = "SLS" Then
For z = 1 To 32 'assumes OFO report run for 52 weeks.
y = 8 'Start with the second week.
GAFS = Cells(x + 3, y + z).Value
Do Until TtlSales > GAFS Or y >= 28 Or y + z >= MySize1 + 9
'Adding 9 to MySize1 simulates the y variable starting at 9.
TtlSales = TtlSales + Cells(x, y + z).Value
If TtlSales > GAFS And y <= 28 Then 'The number 29 is used
to simulate 20 WOS plus the 9 that it starts at.
Cells(x + 3, z + 8).Select
z = 32
If Selection.Interior.ColorIndex <> 6 Then
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Interior.ColorIndex = 4
Else
Selection.Interior.ColorIndex = 41
End If
Cells(ActiveCell.Row - 2, MySize1 + 9).Value = "First WK"
Cells(ActiveCell.Row - 1, MySize1 + 9).Value = "<20 WOS"
Cells(ActiveCell.Row, MySize1 + 9).Value = Cells(8,
ActiveCell.Column).Value
Range(Cells(ActiveCell.Row - 2, MySize1 + 9),
Cells(ActiveCell.Row, MySize1 + 9)).Select
With Selection
.HorizontalAlignment = xlCenter
.ShrinkToFit = True
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle =
xlNone
Selection.NumberFormat = "0"
End If
y = y + 1
Loop
'Debug.Print "ROW - "; x & " Column -" & z & " TTl Sales after
20 wks" & TtlSales & "BOH -" & GAFS
'Cells(x + MyCount1 + 10, z + 8).Value = TtlSales
'Cells(x + MyCount1 + 11, z + 8).Value = GAFS
y = 0
GAFS = 0
TtlSales = 0
Next 'z
End If
Next 'x
End Sub