# Converting numbers to zero between zeros

#### johngio

##### Board Regular
I have a spreadsheet with a date in cell C7, a start time in cell C8 and an end time in cell C9. I want to transfer this incormation across to another column (which is set up in hourly increments. I can do the following:

Sub Downtime()
Dim findit As Range
Set findit = Range("B16:B239").Find(what:=Range("C7").Value, lookat:=xlWhole).MergeArea.Cells(1, 1)
findit.Cells(Range("C8"), 16).Value = 0
findit.Cells(Range("C8"), 17).Value = 0

Set findit = Range("B16:B239").Find(what:=Range("C7").Value, lookat:=xlWhole).MergeArea.Cells(1, 1)
findit.Cells(Range("C9"), 16).Value = 0
findit.Cells(Range("C9"), 17).Value = 0

End Sub

And this will give me zero values at the beginning and end of the downtime: i.e.:

1800___ 1200
0______0
1800___ 1200
1800___ 1200
1800___ 1200
0______0
1800___ 1200

However I would also like to set the values in between to zero so I end up with:

1800___ 1200
0______0
0______0
0______0
0______0
0______0
1800___ 1200

I have tried putting in another couple of lines of formula to check the column and adjust according to the differences between the cells (if the difference is equivalent to 1800, set the next cell to zero), but this seems like a long way of doing this.

Is it possible to do something like:

Sub Downtime()
Dim findit As Range
Set findit = Range("B16:B239").Find(what:=Range("C7").Value, lookat:=xlWhole).MergeArea.Cells(1, 1)
findit.Cells(Range("C8:C9"), 16).Value = 0
findit.Cells(Range("C8:C9"), 17).Value = 0

End Sub

And enter the data over the enter range?

For this example C8 is 6 and C9 is 9.

Cheers

John

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,168,126
Messages
5,857,522
Members
431,883
Latest member
Hien

### 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

### 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