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