Posts by NoSparks

    Re: Auto enter data time


    The way I understand the situation, maybe this

    Re: VBA works on step through, but not when I run


    It won't help other than letting you know that execution has stopped at that point in the sub.


    Maybe we need a definition or executing.
    First thing you have is a message box, did I miss you saying it is displayed?

    Re: Search working but not correct!


    I don't know what to change because I can't see what's happening.


    I suggest you put a break point, F9, on the r=1 line then step through the code one line at a time with the F8 key and flip flop back and forth between the VBE and the sheets to see what actually happens when each line of code is executed.
    You should then see what's being copied, where and what is being pasted, etc.

    Re: Time sheet in Excel


    @ tasha2515


    What happens if the employee takes a 3 hour lunch?


    Based on the formula you have in your attached 'Copy of Express Time Sheet new 1.0 Master.xlsx' at another forum, regular time excludes the lunch break.
    The somewhat similar solution I posted 3 days ago at that forum took this into consideration.


    Had you cross posted in the appropriate manner, as advised on at least 3 forums, I wouldn't have wasted my time.


    Anyway, would you please now have the courtesy to go to all your cross posts and inform you now have a solution and give credit where credit is due.


    Thanks.

    Re: Assigning Values in Array, VBA


    Isn't the array zero based.

    Code
    '
        y = 0
         
        For x = 6 To TotalRows + 6
            ReportInfo(y, 0) = Cells(x, 10) ' Activity
            ReportInfo(y, 1) = Cells(x, 2) ' Name
            ReportInfo(y, 2) = Cells(x, 6) ' Hours
            y = y + 1
        Next x
    '

    Re: DataBodyRange Combine 2 Columns


    I think something like this will work


    but I'd just use a formula in the first data row and double click the drag handle
    =[@[Chq No]]&[@Desc]

    Re: Update cell based on value in another cell


    If you're happy, I'm happy.


    It just didn't make sense to me for items that aren't even started to show as Overdue.


    Don't know if you noticed the one line macro in the sheet module I used to change the formulas.
    When I entered the formula and dragged it down the colors went goofy so I used the macro to change them all in one go.

    Re: Update cell based on value in another cell


    Always test on a copy of the original and make sure you have a backup.


    When you change L11 from Overdue to something else you'll need to blank I11 to prevent L11 changing back to Overdue.
    This could be done with Worksheet_Change similar to SO's example in post #4 to remove the formula and blank I11.


    Good luck with your project
    NoSparks

    Re: Update cell based on value in another cell


    Hi josh


    Have a look and see if this will work.


    I changed the formulas down column "I" to do away with all those days over due in the yet to be used rows.

    Code
    =IF(OR(ISBLANK(F11),ISBLANK(G11),ISBLANK(H11)),"",IF(TODAY()>H11,TODAY()-H11,""))


    Because column I is formulas, need to use the Worksheet_Calculate event.
    I've used column Z to hold a formula in Z7

    Code
    =SUMPRODUCT(--(I9:I219<>Z9:Z219))


    and a copy of I9:I219 in Z9:Z219.
    If this location is a problem it could be made to use a separate sheet if need be.


    If Z7 is 0, the two ranges match so it wasn't an I column calculation that triggered the event.
    If Z7 isn't zero some value in column I is different so the macro proceeds.


    Is changing L11 to 'Complete' (or anything else) after being automatically changed to 'Overdue' something you would normally do?
    If so we'll need to address that too.

    Re: Add new row according to the number key in


    Try this... it uses an extra column to keep track of the 'previous' numbers of column A in order to know how many rows for deletions.
    I've used column P as the extra.