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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,114,380
Messages
5,547,599
Members
410,802
Latest member
DataMgmtAnalyst7
Top