Posts by Batman

    Re: Taken taken minus breaks over midnight


    The formula in column I needs to have an extra condition to the AND function, to test if the Finish Time is less than the start time, to determine whether or not the shift has spanned midnight.


    Hope this helps.

    Re: INDIRECT to look for sheet and check text in cell

    The first formula has a few problems with the brackets:

    =IF(INDIRECT("'"&$A3&"'!$M$4="Please Select"," "),MATCH(INDIRECT("'"&$A3&"'!$M$4"))

    should be something like

    =IF(INDIRECT("'"&$A3&"'!$M$4)="Please Select"," ",MATCH(INDIRECT("'"&$A3&"'!$M$4")))

    And I have no idea what the MATCH function is trying to do. It only contains one of the three arguments it would normally want.

    Re: Loop to find sheets and then give me a popup box

    'Exit For' jumps out of the current For ... Next loop, and goes to the statement following Next.

    Once you have found one sheet that indicates that data is present, there is no point in checking any of the other sheets, as you are not going to change the setting of 'datapresent'. It's just there to make the process a little more efficient.

    Re: Loop to find sheets and then give me a popup box


    Firstly, your code will not compile or run, as the first IF statement does not contain THEN.

    I would then suggest that you properly indent the code, so that the logical flow is apparent. You will then see that the code is checking for 'datapresent = True' within the loop through each sheet within the workbook, rather than just once within the workbook.

    In the code below, I have also replaced the undeclared variables 'Sheet' and 'Book' with the variables you have declared, but not used.

    Re: Sumproduct to calculate differences between two lists

    I thought it would turn out to be more complicated than your original question suggested.

    Personally, I would be finding out exactly what is required before I started developing anything, as the requirements may well determine the approach.

    However, albeit without having seen your data or what you want to develop, that would probably not be my approach. I suspect I would create a single table from the 12 monthly employee lists. Against each row I would manually add (in separate columns) a year number (in case the analysis eventually spans years) and period number. I would then concatenate the two into a single year/period string, in the format yyyy-mm, in another column.

    Depending on the reporting requirements, I would add separate columns to identify (Y or N/blank) whether the person is a joiner or leaver in that particular month. I would then add columns to calcuate whether they have moved out of, or into, the department in the month. Similarly, create any weekly/monthly employee type data.

    Once all the data is in place, I would build a pivot table on the results. You could create a rolling 12 month report by adding on the next month's data, filling down the formulas, etc., and using the pivot table's filters to display just the data you want to see.

    If you opt for a VBA solution, it is likely to be far less flexible than this sort of approach, where you can add extra columns of data whenever you want, and also respond rapidly when users decide they want something slightly different.

    I hope this is of some use.

    Re: VBA: Macro to loop through range and remove certain data after the last hyphen

    Apologies, I thought that would need an error trap, but obviously didn't test it properly, and didn't put one in. Try this.

    Bear in mind my other comments about the logic of using the Worksheet_Change event. It might be correct, but I don't know the circumstances in which the code wants to be triggered.

    Re: VBA: Macro to loop through range and remove certain data after the last hyphen

    Hi jg2703,

    Welcome to the Ozgrid forum.

    I suspect you're looking for something like this, which builds an array of final characters to delete, and compares each set of final characters to the array before removing it.

    However, I would suggest you look at the logic of what you are doing. Do you really want every small change you make to this worksheet, wherever it is and whether or not the rebuild has already taken place, to trigger the macro and rebuild column A? If you don't then you should consider an alternative means of triggering the procedure, one that doesn't involve a worksheet event.

    Re: Sumproduct to calculate differences between two lists


    In this example, March's employee numbers are in A3:A12 and their departments in B3:B12. The equivalent April data is in E3:E12 and F3:F12.

    The formula:

    entered in C3 and filled down will calculate the March employees who left. It's an array formula, so needs to be confirmed with Shift + Ctrl + Enter, not just Enter. I'm sure you can work out the joiners calculation.

    However, what happens if someone changes department? Your logic (or at least how I interpret it) will show them as leavers and joiners, when presumably they are not.

    Re: Method or data member not found

    A few things:

    1. Have you declared the ShellExecute API function, and the variable SW_SHOWNORMAL? That is the only thing that causes a compile error if I copy your code and force a compile.

    2. I'm intrigued by your use of DisplayAlerts, although it shouldn't cause a compile error. Your comment says that you are turning off alerts momentarily, but then turns it on. And vice versa when you turn it back on.

    3. Why are you using Functions, and not Subs? Although the code will probably work, the intended use of a function is to return a value, which is not what is happening.

    Re: Selecting 3 relevant financial years for every company

    Your definition of YR as an array will work, but there are a few points:

    > By default VBA will create a zero-base array, where the first item is number 0 (zero) and not 1
    > You need to assign values to the array
    > Your code is finding ComID, moving 2 cells to the right, and then trying to assign the (empty) value of the array to that cell.

    I suspect that you probably need to put YR(0) etc. before the = sign, rather than after it, as I assume you are trying to assign the value from the worksheet to the array, and not the other way round. However, as pike says, I think we need to understand more about the detail of what you are trying to do.

    Re: Selecting 3 relevant financial years for every company

    It's still not clear whether your error is appearing on the code line starting 'ComID =' or 'rRange.Find'.

    Either way, there are too many variables that we can't see how you have declared them, and what values they might have when these parts of the code are run. Pike asked what the variable 'I' contained, and although you refer to it in your answer, you don't say exactly what value it would have. It should contain an integer to represent a row number.

    In the rRange... command, the contents of the 'After' argument will cause your Type Mismatch error. That needs to contain a VBA range reference, e.g.

    rRange.Find(What:=ComID, After:=S1.Range("U4"), ....

    It will need to refer to the same sheet as rRange. However, there is no indication of what type of variable YR is (it looks to be an array of some sort), and that could also be causing a problem.

    Re: Changing Index Match Lookup Value

    You can use the same principle of INDIRECT, but you will have to go back to using your original IF(ISERROR( format. The IFERROR function avoids Excel having to do the same work twice.


    Re: Userform Unique reference number question

    Hi GhostMonkey72,


    With ReferenceNumber
        .Value = Cells(1, 1).Value & Format(Val(Replace(Cells(Rows.Count, 1).End(xlUp).Value, Cells(1, 1).Value, "")) + 1, "000")
        .Enabled = False
    End With