# Posts by shknbk2

• ## Excel overtime calculation

You could add a break start time and a break end time and figure out which time period it belongs in rather than just saying that there was a 1 hour break somewhere in the shift.

• ## Excel overtime calculation

I think what you are doing wrong is confusing Time decimals with regular math decimals. What do I mean? A half an hour is 30 minutes in Time, while a half is 0.5 in regular math. The 8.3 you got above is correct because you formatted the text to show the hour (8) followed by the minutes (30) separated by a period (the cell formatting was probably to show 1 decimal place; thus, the 8.3 rather than 8.30). To correct for this, you would start needing to separate the hour calculations from the minutes so that you could divide the minutes by 60 to get the correct math decimal value.

However, that is more complicated than it needs to be (I actually started down that road and got the formulas to work, but they were over-complicated).

Try these revised formulas instead. I assume you want the math decimal values so that 7.5 hours means that you worked from 8AM to 3:30PM, for example.

This formula calculates the overtime hours for column H. This adds all time before 7AM to all of the time after 4PM.

Code
1. =TEXT(24*(IF(C6<=TIME(7,0,0),TIME(7,0,0)-C6,0)+IF(D6>=TIME(16,0,0),D6-TIME(16,0,0),0)),"0.00")

This formula for column G adds all hours between Start and End and then subtracts the overtime hours and break hours (i.e., "-(E6+H6)*TIME(1,0,0)").

Code
1. =TEXT(24*(D6-C6-(E6+H6)*TIME(1,0,0)),"0.00")

All of the math is done by adding/subtracting Times so that the result is a decimal value equivalent to the correct percentage of a 24-hour day; thus, the multiplaction by 24 to give a math value of the number of hours. For example, the result of the Normal Hours for 0400 is 0.03125, which multiplied by 24 gives the 0.75 hour result (or 45 minutes if you want to think of it in Time),

• ## Opening a Userform based on Vlookup Variable

Try a Select Case with a case statement for each of the user forms.

Barebones example:

Code
1. Select Case Ret
2. Case "UserForm1"
3. UserForm1.Show
4. Case "UserForm2"
5. UserForm2.Show
6. End Select
• ## clear cell contents based on date in another column

Re: clear cell contents based on date in another column

You could use something like the following code to delete the points on the calendar day that you run it. However, looking at your code, I couldn't figure out where you would want it to occur.

When do you want it to clear the day's points: when you run a macro or some other time?

Code
1. Sub ClearPoints()
2. Dim c As Range, d As Long
3. d = Now() - 0.5
4. On Error Resume Next
5. Set c = Cells.Find(CDate(d), Cells(1, 1))
6. If Not c Is Nothing Then
7. c.Offset(0, 2).Value = ""
8. End If
9. End Sub

Also, the dates for December are wrong. Day 1's date is 1/1/1900, not 12/1/2016. That matters for this code above that uses today's date.

• ## Convert Array of Cell Addresses To Range Without Loop

Re: Convert Array of Cell Addresses To Range Without Loop

If you can get the list of addresses as a comma-separated string, you can use the following function to return the optimized string. It's a little ironic that I used 3 For loops when you asked for not looping through each one!

• ## Worksheet Event_Array

Re: Worksheet Event_Array

Get rid of the parentheses of "arr()" in this line:

Code
1. lng = Application.WorksheetFunction.match(rs, arr(), 0)

Change it to:

Code
1. lng = Application.WorksheetFunction.match(rs, arr, 0)

Also, you will want to stop the procedure from running in a loop every time you set the Target.Value to vbNullString. Substitute this line:

Code
1. Target.Value = vbNullString

for this:

Code
1. Application.EnableEvents = False
2. Target.Value = vbNullString
3. Application.EnableEvents = True
• ## Copy and paste is very slow VBA

Re: Copy and paste is very slow VBA

Try this and see if it is any faster. It may not be, but give it a shot. I haven't tested it because I don't have a workbook set up the same way, but something similar worked on one of my workbooks.

• ## Format Row Color based on cell then change color

Re: Format Row Color based on cell then change color

No need to use a macro. You could do something like this with an extra data column (C in the sample workbook, D in your picture) keeping track of the changes together with Conditional Formatting based on that column.

ozgrid.com/forum/core/index.php?attachment/70777/

You'll need to find a way to extend the formatting and column formulas as the table gets longer . . . perhaps by formatting the data as a table.

• ## Allow user to choose which text file to import

Re: Allow user to choose which text file to import

Refer to this link to find one way of choosing files in Excel. Modify it for your needs. Do this at the beginning of the macro where you can also handle what happens if no file or the cancel button is selected.

In your code, change the Connection text from

Code
1. "TEXT;C:\Users\xxxxx\Documents\xxxxx\example.txt"

to something like

Code
1. "TEXT;" & FName

or whatever variable you set as receiving the selected file name. Make sure the variable has the whole path.

• ## Basic String Manipulation question - Clearing contents of Range

Re: Basic String Manipulation question - Clearing contents of Range

By the way, StevenR is right. I guess I was thinking too hard. That line can be simplified to:

Code
1. If Intersect(c, Range(range1address)) Is Nothing Then
• ## Basic String Manipulation question - Clearing contents of Range

Re: Basic String Manipulation question - Clearing contents of Range

Can you paste your whole code now?

• ## Count Unique Values

Re: Count Unique Values

• ## Count Unique Values

Re: Count Unique Values

Try this to make it a range:

• ## Basic String Manipulation question - Clearing contents of Range

Re: Basic String Manipulation question - Clearing contents of Range

• ## Basic String Manipulation question - Clearing contents of Range

Re: Basic String Manipulation question - Clearing contents of Range

The manual calculation should have nothing to do with it. I wrote the code based on the string values you provided, but maybe the strings you are using in the actual code have a different format. Can you list what the string values are in the project?

• ## Basic String Manipulation question - Clearing contents of Range

Re: Basic String Manipulation question - Clearing contents of Range

What line doesn't work? I should mention that the code doesn't delete the rows just yet. Instead, I put in a Debug.Print to put the non-overlapping cell addresses listed there to double check that the code is working properly. You can see the output in the Immediate window (Ctrl+G from within VBE).

If you want to clear the cells, change

Code

to

Code
1. c.ClearContents
• ## VBA code in Macro to copy data from one sheet to another without pasting formulas

Re: VBA code in Macro to copy data from one sheet to another without pasting formulas

Sarah,

It does make sense. There are lots of websites dealing with questions on how to return a truly empty cell with no direct solution, only workarounds.

So, in a workaround for your case, some additional code could be added to clear the blank cells after pasting the data.

• ## Excel UserForm Object Required error 424

Re: Excel UserForm Object Required error 424

In my experience, when the code stops running and highlights the .Show line, the error is actually in the form code. You can try to step through the code to see where the problem is. Place a breakpoint (F9) on the .Show line and step through (F8) until you get the 424 error within the form code.

• ## Count Unique Values

Re: Count Unique Values

Try this. The function creates an AdvancedFilter copy of just the unique values of rng4 in an out of the way place (the right-most column of the worksheet), counts the values, and then clears the data before returning the number back to Range("A1").

Code
1. ws.Range("A1") = countUniques(rng4)
• ## Basic String Manipulation question - Clearing contents of Range

Re: Basic String Manipulation question - Clearing contents of Range

I'm not coming up with anything. For Each is just so darn handy in this situation. It's too bad there is a Not Intersect function directly.

It's not what you wanted, but this works:

Code
1. Sub subtractRange2()