Offest format

johngio

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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?

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

Forum statistics

Threads
1,141,680
Messages
5,707,788
Members
421,527
Latest member
Tamiwsw

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