Converting numbers to zero between zeros

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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.
Master Excel Bundle

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.

Forum statistics

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

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