Closer to Exact

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
The following is how my data appears on my spreadsheet.
Optimize Production.xls
ABCDEFGH
1PcsPerShift
2450DateDayPerDayShiftsExpectedExpectedProduction
31/17/2005Monday00.0005480
41/18/2005Tuesday00.000OriginalOrderQty.
51/19/2005Wednesday00.0005480
61/20/2005Thursday00.000Difference
71/21/2005Friday548012.1854800
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
ABCDEFGH
1PcsPerShift
2450DateDayPerDayShiftsExpectedExpectedProduction
31/17/2005Monday10962.4413506750
41/18/2005Tuesday10962.441350OriginalOrderQty.
51/19/2005Wednesday10962.4413505480
61/20/2005Thursday10962.441350Difference
71/21/2005Friday10962.4413501270
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
JKLMNO
16DesiredResults
17DateDayPerDayShiftsPlannedProduction
181/17/2005Monday90025481
191/18/2005Tuesday9002OriginalOrderQty.
201/19/2005Wednesday9812.185480
211/20/2005Thursday13503Difference
221/21/2005Friday135031
Sheet1



Any help would be greatfully appreciated.

Thank You,

Kurt
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,147,674
Messages
5,742,541
Members
423,736
Latest member
dracula cyrus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top