Offest format

[johngio]

Hi all,

I have a date in cell C7
In cell C8 I have a start time
In cell C9 I have a finish time

I would like to transfer this data into columns Q and R by making them zero. Currently I have (what is in each column):

DATE blank HOUR tonnes tonnes tonnes Q R

The date column is merged so there are 24 rows together.
After this none of the columns are merged.

Here is the formula:


Sub Downtime()

Set findit = Range("B16:B239").Find(what:=Range("C7").Value, lookat:=xlWhole)
findit.Offset(0, 15).Value = 0

Set findit = Range("B16:B239").Find(what:=Range("C7").Value, lookat:=xlWhole)
findit.Offset(0, 16).Value = 0

End Sub


It works ok, excpet for the fact that the zero appears in hour 1 of each day. For example, if cell C8 contains a 6 it means this occurs at 6 in the morning. I have tried chaning my formula to:
findit.Offset(0+Range("C8"),16).Value=0

However this seems to put a zero 24 places too low (as in the correct time, but for the next day).

Is it possible that I need to put an offset within my offset to make this work?

I hope this is not too confusing.

Cheers

John

 

[Andrew Poulsom]

Yet another issue with merged cells, which to my mind cause more problems than they solve. Offset seems to us the last cell in the MergeArea. But Cells seems to use the first cell.

Does this get you closer to what you want?

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

 

[johngio]

Thats exactly what I was trying to do.

Thanks