Posts by Batman

Re: Taken taken minus breaks over midnight

Hi,

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.

=IF(AND($B5<=I$2,$C5>=I$3,$C5<$B5),I$3-I$2+1,0)

Hope this helps.

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

:redface:

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

If the INDIRECT function returns a single cell, as I suspect it will do, then just remove the MATCH function, and associated brackets.

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

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

Hi,

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.

Sub Test()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim button As Integer
    Dim datapresent As Boolean
    
    For Each wb In Application.Workbooks
        datapresent = False
        For Each sh In wb.Worksheets
            If sh.Name = "Sheet1" Or sh.Name = "Sheet2" Or sh.Name = "Sheet3" Then
                datapresent = True
                Exit For
            End If
        Next sh
        If datapresent = True Then
            button = MsgBox("Data already present. Do you want to delete and re-format?", vbQuestion + vbYesNo + vbDefaultButton2, "Continue?")
        End If
    Next wb
End Sub

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.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long, Pos As Long, i As Long
    Dim CellValue As String, EndChars As String
    Dim FindChars() As Variant, intMatchPos As Integer
    
    FindChars = Array("-S", "-M", "-09", "-10")
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        Application.EnableEvents = False
        
        For i = 1 To LastRow
            CellValue = .Cells(i, "A").Value
            Pos = InStrRev(CellValue, "-")
            If Pos > 0 Then
                EndChars = Mid(CellValue, Pos, 99)
                
                intMatchPos = -99
                On Error Resume Next
                intMatchPos = WorksheetFunction.Match(EndChars, FindChars, 0)
                On Error GoTo 0
                
                If intMatchPos >= 0 Then
                    .Cells(i, "A").Value = Left(CellValue, Pos - 1)
                End If
            End If
        Next i
        
        Application.EnableEvents = True
    End With
End Sub

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: Autofill CELLS based on another CELL.

Hi nonqoqo,

Welcome to the Ozgrid forum.

This would probably work slightly more efficiently with helper columns, but I believe is what you are looking for. Fill down the formula cells for as many rows as needed.

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.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long, Pos As Long, i As Long
    Dim CellValue As String, EndChars As String
    Dim FindChars() As Variant
    
    FindChars = Array("-S", "-M", "-09", "-10")
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        Application.EnableEvents = False
        
        For i = 1 To LastRow
            CellValue = .Cells(i, "A").Value
            Pos = InStrRev(CellValue, "-")
            If Pos > 0 Then
                EndChars = Mid(CellValue, Pos, 99)
                If IsNumeric(WorksheetFunction.Match(EndChars, FindChars, 0)) Then
                    .Cells(i, "A").Value = Left(CellValue, Pos - 1)
                End If
            End If
        Next i
        
        Application.EnableEvents = True
    End With
End Sub

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: Deselect Cells That Have Been Copied

Hi OldFella,

Try

Application.CutCopyMode = False

Re: Sumproduct to calculate differences between two lists

Hi,

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:
=IF(ISERROR(MATCH(A3&B3,$E$3:$E$12&$F$3:$F$12,0)),"LEAVER","")

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: Determine if &quot;Active&quot; if hire and term date are within cells range

Hi Delta729,

Welcome to the Ozgrid forum.

Try looking at it the other way round, and work out who is not active:

=IF(OR(AND(N([@Term])>0,[@Term]<$I$2),[@Start]>$J$2), "","Active")

Hope this helps.

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: Selecting 3 relevant financial years for every company

Hi momosiew14,

Welcome to the Ozgrid forum.

In accordance with the rules you signed up to, please edit your post to add code tags to the VBA code. The easiest way to do this is to highlight the code section and click the # icon.

Re: Multiple IF Statements

As far as I can see, this can be abbreviated to

=MIN((SEC*LAB)+(SEC*LEC),SEC*(1.5*SEC))

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.

=IF(ISERROR(INDEX(D:D,MATCH(INDIRECT(G2&ROW()),C:C,0),1)),"",INDEX(D:D,MATCH(INDIRECT(G2&ROW()),C:C,0),1))

Re: Userform Unique reference number question

Hi GhostMonkey72,

Try:

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