Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count number of weeks missing for each individual in a database | Excel Worksheet Functions | |||
count missing field | Excel Discussion (Misc queries) | |||
Missing count of age to a group of ages | Excel Worksheet Functions | |||
Toolbars Missing, And option to Add Missing | Excel Discussion (Misc queries) | |||
auto filter missing record count & a fix idea | Excel Programming |