seperate for shift changes in workwsheet 1

[tyone]

i am trying to figure out a way to add three rows after i find a particular time. i have columns b and d set formated as time columns. i am looking for a way to go thru the worksheet and add three rows after i find a time of 7:00. the time can be either in column b or d or fall between the two columns as per example:

b d
7:00 9:00
5:00 7:00
5:00 10:00

so if i find it the value of 7:00 and not any value that has 7:00 in it like 17:00 i want to add three rows beneth that row. i am assuming it must be some form of a if condition but don't have a clue as to how to code it. i usually code in rpg have coded in c++ but that was years ago.
so any help would be greatfull.

Thanks
don

 

[SkipVought]

Hi,

First...

Why do Dates and Times seem to be so much trouble? faq68-5827

Now you know that TIME is the fractional part of a number. TIME is not a string.

So what code do you have so far that is not working for you?

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

A starting point:
If TimeValue(Range("B" & lngRow)) <= #7:00# And _
TimeValue(Range("D" & lngRow)) >= #7:00# Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tyone]

hi skipVoight,

As of this point of time i have no code. i have looked at it thru the find feature in excel and recorded that into a macro and looked at that but that seems to be looking at one cell at a time and not for what falls between the ranges. i am not sure if it is a lookup of some form or a if condition. i think that i need to use the range feature:

Range("B:B,D").Select

but not sure on that one either. just need a push in the right direction is what i am looking for...

 

[SkipVought]

Perhaps you ought to explain WHAT your are trying to do, not HOW you are trying to do it. What is the business case?

Skip,
[sub]
[red][/red]
[/sub]

 

[tyone]

skip the spreadsheet is from is a access database from our extrusion press. i am trying to make so that we can keep track of how many pounds of metal, billets is pressed per shift, and daily totals. so what i am trying to do is find where the shift changes are 7:00 15:00 and 23:00 and then i will do the subtotals from there. also if the shift overlaps one to the next i will have to break down how many billets were pushed on the first shift and the rest will go the next shift. along with total time the extrusion press was in operation and what the down time for each shift.

i hope that explains it little more or better.

 

[SkipVought]

You ought to be able to QUERY the access database using MS Query to get the infor that you need. You can use an IIF statement to break out the shifts. Excel has a plethora of data analysis tools that will make the job possible.

It's almost always KLUNLY to do a bunch of inserts, shifting stuff all over the place. I AVOID that like the plague, if at all possible.

Skip,
[sub]
[red][/red]
[/sub]

 

[tyone]

hey phv i am getting a runtime error 1004 at the timevalue when i try to step thru this any ideas as to why.

 

[SkipVought]

Please post your code and indicate the statement with the error.

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

Which line of code highlighted when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tyone]

i added the range because i thought it was getting stuck on the first row which is a header row.

but when i step thru this it errors out on the timevalue statements.

Range("B2:B500,D2500").Select
Do While x = 0

If TimeValue(Range("B") <= #7:00:00 AM#) And _
TimeValue(Range("D") >= #7:00:00 AM#) Then
addr = ActiveCell
Rows("addr:addr").Select
Range("addr").Activate
Selection.Insert shift:=x3down
x = 1
End If

Loop

 

[PHV]

Range("B") isn't a valid range ...
Yet another starting point:
Dim lngRow As Long
For lngRow 2 To 500
If TimeValue(Range("B" & lngRow)) <= #7:00# And _
TimeValue(Range("D" & lngRow)) >= #7:00# Then
MsgBox "Row " & lngRow & " found !"
Exit For
End If
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Maybe something like this...

    Do While [b]x < 500[/b]
       
    If TimeValue(Range([b]"B" & x[/b]) <= #7:00:00 AM#) And _
       TimeValue(Range([b]"D" & x[/b]) >= #7:00:00 AM#) Then
       [b]set addr = cells("A" & x)
       set addr = range(addr, addr.offset(2)).entirerow
       addr.Insert shift:=xldown
       x = x + 1[/b]
    End If
    
    Loop

Skip,
[sub]
[red][/red]
[/sub]

 

[tyone]

phv could i use "b:b" as the range and the same as d

 

[PHV]

Range("B:B") is an entire column, you want that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tyone]

wouldn't that only loop until it hit the first 7:00 then step out of the loop.

 

[PHV]

Have you just even tried to play with my suggestion stamped 9 Jan 07 16:23 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tyone]

PHV that code gets a error 13: type mismatch at the timevalue statements. any ideas as to why.

 

[PHV]

any ideas as to why
Probably at least one cell in B2:B500 or D2500 couldn't be converted to a valid Time value ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886