Add VBA to extract hours 2 times (in and out)

[g1terra]

Hello,

I am trying add to this code I started building and stuck on adding VBA to extract hours from 2 times (columns). The problem is I need to get these hrs, but then -.50. So for example if the hours extracted from 2 times is 7hs I want the result to be 6.5hrs (hence the -.50).

Here is the attachment and what is highlighted in yellow is where I would like the hours calculate (Columns C:I).

Any help would be MUCH APPRECIATED!

Here is the code.

Please Login or Register  to view this content.

[GlennUK]

I cannot open that file ... some kind of corruption. Can I ask why you aren't doing this via simple formulas?

[jaslake]

I also cannot open the Attachment...it appears to be corrupt...suggest you try to reattach the sample file...

[g1terra]

Ok sorry about that, lets try this again. I cant use a simple formula because this is an export from a labor program. I was going to use a work around and place a macro button and set users up with a personal file that holds this macro.

The part I did not add yet needs to find the total hrs scheduled. So 8am - 4pm = 8 hrs. I highlighted the column in yellow where I need it to put the scheduled Hrs.

Also this code works kind of slow. The export we will be using is only 1 sheet BUT the rows could be the same or could be more stations/hrs.

Hope that makes sense

Try this

[GlennUK]

What rules are to apply? For example, if the Total Hours is less than 6 hours (or less than 5, or 4) ... will the 0.5 hr still need to be subtracted?

Will you want the result in time format?

In your code, in a couple of places you have Cells.Select ... which selects every available cell in the sheet ... more than 17 billion cells ... that would slow your code down for any processing related to Selection. I'd use the UsedRange object instead.

[g1terra]

No rules. I just need the calculation of hrs then minus 30 minutes. The 30 minuets are planned break periods in which they would punch out for

I here you on cells select. I am a novice with VBA. I’m not too familiar with Used range object. Any help with that would be appreciated

[GlennUK]

Hi,

time calc code:

Please Login or Register  to view this content.

Your other code, tweaked:

Please Login or Register  to view this content.

[g1terra]

When i copy and paste that into my code i am having debug issues. Its probably me, like I said I am a novice and having a hard time getting your recommended changes and the time cal added to the original code. Can you help put this together in one code? I have tried lots of variations, i think i am missing something,

This was the original.

Please Login or Register  to view this content.

[g1terra]

Now its getting hung up here with a debug issue. I'm sorry I'm a mess with this..

Please Login or Register  to view this content.

[GlennUK]

I'm guessing you had a setup that showed Page breaks or similar, but don't have that now. Remove that code.

[g1terra]

It was to set page breaks. There is no way I can keep that? It’s kind of important

[GlennUK]

I've never used Page breaks myself ... did that code work before? What happens when you manually set things the way you want, and Record Macro ... what does that code look like?

[g1terra]

I will delete and and try that piece over again. Can you help me place your modification recommendation and the time calculation into my original above. You posted modifications but I seem to be missing something that wont work. The calc part doesn’t seem to work for me

[g1terra]

Ok here is the final code. It may be a little slow (like 60 seconds to run) but it works.

If anyone knows how to make the result the hrs variance highlight yellow please let me know.

Please Login or Register  to view this content.

[GlennUK]

Hi again, here is your code, tweaked, so as to not process millions of cells unnecessarily:

Please Login or Register  to view this content.

... but I don't know what you mean by "make the result the hrs variance highlight yellow" ... can you clarify?

[g1terra]

That worked!!

I am adding a part that highlights the hrs over 7.4. I added a call in this macro to do it but it highlights more than what I want for some reason.

Here is the entire code, you can see the Call Highlight at the end.

Please Login or Register  to view this content.

[g1terra]

Here it is in the sheet. I only want it to highlight the scheduled hrs (when over 7.4).

[g1terra]

Ok i have everything working except this code only highlights Column I (over 7.4) not Column C as well

Please Login or Register  to view this content.

[g1terra]

So the issue here is this part..

Please Login or Register  to view this content.

[g1terra]

I figured that last part of my code.

Please Login or Register  to view this content.