Posts by shknbk2

    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),

    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.

    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!


    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

    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.


    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.

    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.

    Re: Count Unique Values


    Try this to make it a 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
    1. Debug.Print c.Address


    to


    Code
    1. c.ClearContents

    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.


    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.

    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").



    The only change I made in your sub was

    Code
    1. ws.Range("A1") = countUniques(rng4)

    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()
    2. range1address = "A12:A15,A17:D20"
    3. range2address = "A10:A13"
    4. For Each c In Range(range2address)
    5. If Intersect(c, Intersect(Range(range2address), Range(range1address))) Is Nothing Then
    6. Debug.Print c.Address
    7. End If
    8. Next
    9. End Sub


    The only other way I can think of is using arrays, but that would use loops as well.