# 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

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)

Replies
5
Views
45
Replies
9
Views
134
Replies
8
Views
65
Replies
8
Views
97
Replies
7
Views
28