Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|