# Closer to Exact

#### stapuff

##### Well-known Member
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).

Replies
9
Views
184
Replies
19
Views
806
Replies
11
Views
409
Replies
18
Views
808
Replies
9
Views
234

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.

### Which adblocker are you using?

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

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