Missing something? does not count correctly
 

Can someone tell me what is wrong with this?
I guess I'm going about it the wrong way. I have a row (96 cells) tha
will only be partially populated. Where evere it starts I need to begi
counting from that cell. Then based on the cells that have the valu
"IND" in them insert the values below.

Thanks

c = 0
for each cell. range("G11,CX298")
if cell.value < "" then
c = c + 1
If cell.value = "IND" _
and c = 8 then
cell.offset(0,9).value = "Brk"

If cell.value = "IND" _
and c = 16 then
cell.offset.value(0,16) = "Lun"
cell.offset.value(0,17) = "Lun"

If cell.value = "IND" _
and c = 24 then
cell.Offset(0,25)value = "Brk"

If cell.value = "IND" _
and c = 32 then
cell.Offset(0,32)value = "Brk"

if cell.value = "" then
c = 0
end if (5 times)
nex

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

Missing something? does not count correctly
 

for starters:

2 Cells:
? range("G11,CX298").Address
$G$11,$CX$298

27648 cells:
? range("G11","CX298").Address
$G$11:$CX$298

but it seems improbable that you want to either loop over that entire range
or even just the two cells.

It seems like you would want to walk down a single column, then for each row
in the column, work with the cells in that row.

is the pattern 8 IND's and either brk or lunch, for a total of 9 cells?

Dim cell as Range, cell1 as Range
Dim bLunchDue as boolean, bHadLunch as boolean

for each cell in Range("G11:G298")
bLunchDue = False
bHadLunch = False
' now walk across the row or reference cells in the row.
for each cell1 in cell.Resize(1,96-7)
if application.Countif(Cell1.Resize(1,8),"IND") = 8 then
if bLunchDue then
cell1.offset(0,8).Value = "lun"
bLunchdue = False
bHadLunch = True
else
cell1.offset(0,8).Value = "brk"
if not bHadLunch then bLunchDue = True
end if
end if
Next
Next

--
Regards,
Tom Ogilvy


"hotherps " wrote in message
...
Can someone tell me what is wrong with this?
I guess I'm going about it the wrong way. I have a row (96 cells) that
will only be partially populated. Where evere it starts I need to begin
counting from that cell. Then based on the cells that have the value
"IND" in them insert the values below.

Thanks

c = 0
for each cell. range("G11,CX298")
if cell.value < "" then
c = c + 1
If cell.value = "IND" _
and c = 8 then
cell.offset(0,9).value = "Brk"

If cell.value = "IND" _
and c = 16 then
cell.offset.value(0,16) = "Lun"
cell.offset.value(0,17) = "Lun"

If cell.value = "IND" _
and c = 24 then
cell.Offset(0,25)value = "Brk"

If cell.value = "IND" _
and c = 32 then
cell.Offset(0,32)value = "Brk"

if cell.value = "" then
c = 0
end if (5 times)
next


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

Missing something? does not count correctly
 

Tom,
You were correct in your assumptions about the range. Moving throug
the columns makes better sense.

To answer your other question, yes the pattern is for INDS and eithe
one Brk or 2 Lun (each cell 15 mins) Lun = 30mins.

After pasting in it, what it does is adds "Brk" at the end of eac
group of "IND"

shift = 12:00AM to 8:30 AM (35 cells)
So if you can picture this, an 8 hour shift would have 8 cells pe
every two hours. IND can appear in any of the two hour groups. S
somehow the code has to count how far into the shift it is?

If IND is in the second group of 8 cells (2AM to 4AM)Lun,Lun shoul
followthat group.

IF IND is in the third group(4AM to 6AM) Brk should be inserted onc
around 6AM

IF it is a 10 hour shift or more, (forget that I'll try and figure tha
out myself)

I'm going to work on it now, but a few questions:

What makes bLunch due initially?

Thanks

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

Missing something? does not count correctly
 

Nevermind Tom, my mistake again.

What I failed to tell you is that everytime 8 "IND"s are added the cod
drops down to the next row or further.

IND does not always meet the criteria of every row.

So now let me try to figure out how to integrate into my code tha
loops through the rows.

Oh by the way, I knew what you gave me would work so I copied IN
across a whole row, and it worked just as I'm sure you intended.

Thanks again

back to work..

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