Values run out of range
 

Hi I'm using the following code, that places text values in a row a
specific points in the row. The problem is that sometimes the value
exceed the range of the defined range that I'm working in

The overall range is G11:CX15

But if a cell has a value greater than "" and that value is less tha
32 cells from end of the range. It will start adding values out of th
range.

Is there a way I can make the code stopanytime it tries to go beyond C
?

Thanks.

For Each cell In Range("G11:CP287")
If cell.Value "" Then
If (cell.Offset(0, -1) = "") Then
cell.Offset(0, 7).Value = "Brk"
cell.Offset(0, 14).Value = "Lun"
cell.Offset(0, 15).Value = "Lun"
cell.Offset(0, 24).Value = "Brk"

End If
End I

--
Message posted from http://www.ExcelForum.com

Values run out of range
 

cell.Offset(0, 24).Value = "Brk"

? RANGE("cp1").Offset(0,24).Address
$DN$1

think you need to do some more counting; CP is too far. You can't go beyond
BZ

? RANGE("BZ1").Offset(0,24).Address
$CX$1

Also, it the overall range is G11:CX15, why do you loop G11:CP287?


Any loop G11:BZ15 or G11:BZ287

--
Regards,
Tom Ogilvy


"hotherps " wrote in message
...
Hi I'm using the following code, that places text values in a row at
specific points in the row. The problem is that sometimes the values
exceed the range of the defined range that I'm working in

The overall range is G11:CX15

But if a cell has a value greater than "" and that value is less than
32 cells from end of the range. It will start adding values out of the
range.

Is there a way I can make the code stopanytime it tries to go beyond CP
?

Thanks.

For Each cell In Range("G11:CP287")
If cell.Value "" Then
If (cell.Offset(0, -1) = "") Then
cell.Offset(0, 7).Value = "Brk"
cell.Offset(0, 14).Value = "Lun"
cell.Offset(0, 15).Value = "Lun"
cell.Offset(0, 24).Value = "Brk"

End If
End If


---
Message posted from http://www.ExcelForum.com/

Values run out of range
 

Sorry about that the overall range is G11:CX287

I tried to stop it at CP so it would not go beyond the range

The code creates a work shift 32 columns wide ,and the offsets add i
the breaks and lunchs. The overall range is an entire day starting a
12:00am
at 15 minute intervals until 11:45PM.

My problem is that if someone works a late shift let's say they star
at 7PM(CE11) I need to set up a break for them but not a lunch and
second break because it will go out of the range.

Thank

--
Message posted from http://www.ExcelForum.com