vba help
 

Hello,

I am working on creating a Weeks of supply calc that would be a loop within
VBA and I am hoping I can get some help here. Below is a sample table.

Item OH Wk1 Wk2 Wk3 Wk4 Wk5 WOS
1 6 2 3 1 0 0 ?
2 10 1 4 3 2 5 ?
3 8 1 2 4 3 1 ?


In the table above, I have the current OH for each of the items listed. The
calculation for weeks of supply would be calculated in that the current OH -
each number to its right until it hits 0. Example, item 1 would have 3 weeks
of supply. Item 2 would have 4 weeks of supply. Item 3 would have 3.3 weeks
of supply.

Any help on VBA for this would be greatly appreciated.

Chris

vba help
 

assume 1 6 2 3 1 0 0 is in row 2 starting in A2, then in H2 put the formula

=if(sum(C2:G2)0,B2/Average(C2:G2),"--")

then drag fill down the column.

This doesn't produce your results, but uses the average demand per week.
You would have to show how you come up with you values. I get
5
3.333333
3.636364


--
Regards,
Tom Ogilvy

"Chris" wrote in message
...
Hello,

I am working on creating a Weeks of supply calc that would be a loop
within
VBA and I am hoping I can get some help here. Below is a sample table.

Item OH Wk1 Wk2 Wk3 Wk4 Wk5 WOS
1 6 2 3 1 0 0 ?
2 10 1 4 3 2 5 ?
3 8 1 2 4 3 1 ?


In the table above, I have the current OH for each of the items listed.
The
calculation for weeks of supply would be calculated in that the current
OH -
each number to its right until it hits 0. Example, item 1 would have 3
weeks
of supply. Item 2 would have 4 weeks of supply. Item 3 would have 3.3
weeks
of supply.

Any help on VBA for this would be greatly appreciated.

Chris

vba help
 

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



"Tom Ogilvy" wrote:

assume 1 6 2 3 1 0 0 is in row 2 starting in A2, then in H2 put the formula

=if(sum(C2:G2)0,B2/Average(C2:G2),"--")

then drag fill down the column.

This doesn't produce your results, but uses the average demand per week.
You would have to show how you come up with you values. I get
5
3.333333
3.636364


--
Regards,
Tom Ogilvy

"Chris" wrote in message
...
Hello,

I am working on creating a Weeks of supply calc that would be a loop
within
VBA and I am hoping I can get some help here. Below is a sample table.

Item OH Wk1 Wk2 Wk3 Wk4 Wk5 WOS
1 6 2 3 1 0 0 ?
2 10 1 4 3 2 5 ?
3 8 1 2 4 3 1 ?


In the table above, I have the current OH for each of the items listed.
The
calculation for weeks of supply would be calculated in that the current
OH -
each number to its right until it hits 0. Example, item 1 would have 3
weeks
of supply. Item 2 would have 4 weeks of supply. Item 3 would have 3.3
weeks
of supply.

Any help on VBA for this would be greatly appreciated.

Chris

vba help
 

Sub calcWeeksofSupply()
Dim cell As Range
Dim wks As Double
Dim i As Long
Dim res As Long, tot As Long
Dim res1 As Long, tot1 As Long
For Each cell In Range("B2:B4")
wks = 5
For i = 1 To 5
tot = Application.Sum(cell.Offset(0, 1).Resize(1, i))
If cell.Value <= tot Then
If cell.Value < tot And i 1 Then
tot1 = Application.Sum(cell.Offset(0, 1).Resize(1, i - 1))
res = tot - tot1
res1 = cell.Value - tot1
wks = i - 1 + res1 / res
ElseIf cell.Value < tot And i = 1 Then
wks = cell / tot
ElseIf tot = cell.Value Then
wks = i
End If
Exit For
End If
Next
cell.Offset(0, 6) = wks
Next
End Sub


produced

3
4
3.333

lightly tested

--
Regards,
Tom Ogilvy




"Chris" wrote in message
...
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



"Tom Ogilvy" wrote:

assume 1 6 2 3 1 0 0 is in row 2 starting in A2, then in H2 put the
formula

=if(sum(C2:G2)0,B2/Average(C2:G2),"--")

then drag fill down the column.

This doesn't produce your results, but uses the average demand per week.
You would have to show how you come up with you values. I get
5
3.333333
3.636364


--
Regards,
Tom Ogilvy

"Chris" wrote in message
...
Hello,

I am working on creating a Weeks of supply calc that would be a loop
within
VBA and I am hoping I can get some help here. Below is a sample
table.

Item OH Wk1 Wk2 Wk3 Wk4 Wk5 WOS
1 6 2 3 1 0 0 ?
2 10 1 4 3 2 5 ?
3 8 1 2 4 3 1 ?


In the table above, I have the current OH for each of the items
listed.
The
calculation for weeks of supply would be calculated in that the
current
OH -
each number to its right until it hits 0. Example, item 1 would have
3
weeks
of supply. Item 2 would have 4 weeks of supply. Item 3 would have
3.3
weeks
of supply.

Any help on VBA for this would be greatly appreciated.

Chris