The following is how my data appears on my spreadsheet.
On command button click of this macro:
Sub Button1_Click()
Dim zerocell As Range
Dim c As Range, rng As Range
Dim z As Range, zerorange As Range
Dim x As Long
On Error GoTo ErrorHandler
Set rng = Range("D3:D" & Range("D65000").End(xlUp).Row)
For Each c In rng
If c.Value = 0 Then
Set zerocell = c
x = 0
Do While zerocell.Value = 0
x = x + 1
If zerorange Is Nothing Then
Set zerorange = zerocell
Else
Set zerorange = Union(zerorange, zerocell)
End If
Set zerocell = zerocell.Offset(1, 0)
Loop
For Each z In Union(zerorange, zerocell)
z.Value = zerocell.Value / (zerorange.Count + 1)
Next z
Set zerorange = Intersect(Range("B3"), Range("C3"))
End If
Next c
ErrorHandler:
End Sub
Basically the maco (thanks to martinee) looks for the first 0 in the range, continues down until if finds a value > than 0, then averages the value by the range so my data then appears like the following spreadsheet.
I need the data to appear like the following spreadsheet. Instead of taking the average of the range (example is 2.44 shifts a day) to schedule production more exact.
Any help would be greatfully appreciated.
Thank You,
Kurt
| Optimize Production.xls | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | PcsPerShift | |||||||||
| 2 | 450 | Date | Day | PerDay | Shifts | Expected | ExpectedProduction | |||
| 3 | 1/17/2005 | Monday | 0 | 0.00 | 0 | 5480 | ||||
| 4 | 1/18/2005 | Tuesday | 0 | 0.00 | 0 | OriginalOrderQty. | ||||
| 5 | 1/19/2005 | Wednesday | 0 | 0.00 | 0 | 5480 | ||||
| 6 | 1/20/2005 | Thursday | 0 | 0.00 | 0 | Difference | ||||
| 7 | 1/21/2005 | Friday | 5480 | 12.18 | 5480 | 0 | ||||
Sheet1 | ||||||||||
On command button click of this macro:
Sub Button1_Click()
Dim zerocell As Range
Dim c As Range, rng As Range
Dim z As Range, zerorange As Range
Dim x As Long
On Error GoTo ErrorHandler
Set rng = Range("D3:D" & Range("D65000").End(xlUp).Row)
For Each c In rng
If c.Value = 0 Then
Set zerocell = c
x = 0
Do While zerocell.Value = 0
x = x + 1
If zerorange Is Nothing Then
Set zerorange = zerocell
Else
Set zerorange = Union(zerorange, zerocell)
End If
Set zerocell = zerocell.Offset(1, 0)
Loop
For Each z In Union(zerorange, zerocell)
z.Value = zerocell.Value / (zerorange.Count + 1)
Next z
Set zerorange = Intersect(Range("B3"), Range("C3"))
End If
Next c
ErrorHandler:
End Sub
Basically the maco (thanks to martinee) looks for the first 0 in the range, continues down until if finds a value > than 0, then averages the value by the range so my data then appears like the following spreadsheet.
| Optimize Production.xls | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | PcsPerShift | |||||||||
| 2 | 450 | Date | Day | PerDay | Shifts | Expected | ExpectedProduction | |||
| 3 | 1/17/2005 | Monday | 1096 | 2.44 | 1350 | 6750 | ||||
| 4 | 1/18/2005 | Tuesday | 1096 | 2.44 | 1350 | OriginalOrderQty. | ||||
| 5 | 1/19/2005 | Wednesday | 1096 | 2.44 | 1350 | 5480 | ||||
| 6 | 1/20/2005 | Thursday | 1096 | 2.44 | 1350 | Difference | ||||
| 7 | 1/21/2005 | Friday | 1096 | 2.44 | 1350 | 1270 | ||||
Sheet1 | ||||||||||
I need the data to appear like the following spreadsheet. Instead of taking the average of the range (example is 2.44 shifts a day) to schedule production more exact.
| Optimize Production.xls | ||||||||
|---|---|---|---|---|---|---|---|---|
| J | K | L | M | N | O | |||
| 16 | DesiredResults | |||||||
| 17 | Date | Day | PerDay | Shifts | PlannedProduction | |||
| 18 | 1/17/2005 | Monday | 900 | 2 | 5481 | |||
| 19 | 1/18/2005 | Tuesday | 900 | 2 | OriginalOrderQty. | |||
| 20 | 1/19/2005 | Wednesday | 981 | 2.18 | 5480 | |||
| 21 | 1/20/2005 | Thursday | 1350 | 3 | Difference | |||
| 22 | 1/21/2005 | Friday | 1350 | 3 | 1 | |||
Sheet1 | ||||||||
Any help would be greatfully appreciated.
Thank You,
Kurt
