Finding the first certain value with conditions

[Ramballah]

Hi,


I am looking for a formula that can find the first certain value if its there that also has certain conditions met.
First let me show you a simpler version of my spreadsheet:

So what I need is to find the very first "4" in the row that does not have one next to it, and the last "4" that does not have one next to it. I just need this per row, so not for all 4 rows in 1 formula, just 1 formula for 1 row.
But the issue comes as follows:
1. As you can see, the first 4 starts on column B which is the first of the month, However this can change dynamically from any column: A,B,C,D,E,F and G. Since not every month starts on the monday
2. As you can see, the last 4 is on column AF which is the last date of the month, However this can change dynamically from any column: AE,AF,AG,AH,AI,AJ and AK. Since not every month ends on a wednesday.
3. I only need to find this 4 if its on the 1st of the month or on the last day (28, 29, 30 or 31) of the month
4. The "4"'s need to be a single 4, and not paired up like B3:C3 or AE4:AF4
5. It can also happen that there is not a single 4 on the first or last day but just empty. That's fine I don't need those and I don't need the pairs.
6. If it's any important, there are other values used in the cells aswell, so isblank is not gonna be helpful.
So for row 3 there is one "4", row 4 has one "4", row 5 has one "4" and row 6 has none.
If it gives a clarification for someone: 4's are always paired, and the month ends for example on the 31st, then in the next month the other 4 will be on the 1st making it a pair. So I just need to find those 4's that are at the end/beginning of the months.

I hope someone can finally help me with this!

Thanks in advance,
Ramballah

 

[mumps]

Your request is rather complicated. I'm not sure that I can provide a solution but I will give it a try. It would be helpful if you could fill out the chart below with the appropriate number of hours for each number in column A and post the chart here. Feel free to change it if that would help.

Book1
	A	B	C	D	E
1	Number	Hours on Weekend	Hours not on Weekend	1st of the Month	End of Month
2	1			****	****
3	2			****	****
4	3			****	****
5	4 Single	****	****
6	4 Pairs			****	****
Sheet1

 

[Ramballah]

Your request is rather complicated. I'm not sure that I can provide a solution but I will give it a try. It would be helpful if you could fill out the chart below with the appropriate number of hours for each number in column A and post the chart here. Feel free to change it if that would help.

Book1
	A	B	C	D	E
1	Number	Hours on Weekend	Hours not on Weekend	1st of the Month	End of Month
2	1			****	****
3	2			****	****
4	3			****	****
5	4 Single	****	****
6	4 Pairs			****	****
Sheet1

Sorry for the late reply. I added column F. It can appear that the first of the month is a saturday or sunday, then it needs to be 6h instead of 5,5. I hope this helps!

Book1
	A	B	C	D	E	F
1	Number	Hours on Weekend	Hours not on Weekend	1st of the Month	End of Month	1st of the month weekend
2	1	6	5,5	****	****	****
3	2	7,5	7,5	****	****	****
4	3	6,5	7,5	****	****	****
5	4 Single	****	****	5,5	10	6
6	4 Pairs	16	15,5	****	****	****
Sheet1

 

[mumps]

The cells in row 5 of you sheet contain this formula:

=IF(DAY(JanSun1)=1,"",IF(AND(YEAR(JanSun1+1)=CalenderYear,MONTH(JanSun1+1)=1),JanSun1+1,""))

Because the formula refers to a variable (JanSun1) which is not included in the file you posted, all those formulae in row 5 return an error. I need those formulae to work properly because the macro has to check if a single 4 is located at the first or last day of the month. Could you upload a copy of your file where the formulae are returning an actual value instead of an error (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com and post a link to the file here?

 

[Ramballah]

The cells in row 5 of you sheet contain this formula:

=IF(DAY(JanSun1)=1,"",IF(AND(YEAR(JanSun1+1)=CalenderYear,MONTH(JanSun1+1)=1),JanSun1+1,""))

Because the formula refers to a variable (JanSun1) which is not included in the file you posted, all those formulae in row 5 return an error. I need those formulae to work properly because the macro has to check if a single 4 is located at the first or last day of the month. Could you upload a copy of your file where the formulae are returning an actual value instead of an error (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com and post a link to the file here?

Hi,

Tobey Reuver sent you 1 item

Rooster hof van hersbeek.xlsx

www.dropbox.com

I hope this works, this is the entire workbook. If you manage to make it work for january or just 1month, i can just copy it into the other months so dont worry about all the months.

 

[mumps]

Make the January sheet the active sheet and try this macro. I get slightly different totals for some in column B than what you had. Please double check to see which totals are correct and which totals are not correct and let me know. Perhaps you can figure out how any incorrect totals are being calculated based on your criteria. If we can get this to work properly, it should work on any of your sheets.

Sub CalculateTotals()
    Application.ScreenUpdating = False
    Dim v As Variant, lRow As Long, lCol As Long, r As Long, c As Long, lDay As Long
    Dim total As Double, total1 As Double, total2 As Double, total3 As Double, total4S As Double, total4P As Double
    lCol = Cells(6, Columns.Count).End(xlToLeft).Column '39
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    lDay = Format(CDate(WorksheetFunction.EoMonth(Range("B5"), 0)), "dd")
    v = Range("C5:C" & lRow).Resize(, lCol - 2).Value
    For r = LBound(v) + 2 To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If v(r, c) <> "" Then
                Select Case v(r, c)
                    Case Is = "1"
                        If v(2, c) = "Za" Or v(2, c) = "Zo" Then
                            total1 = total1 + 6
                        Else
                            total1 = total1 + 5.5
                        End If
                    Case Is = "2"
                        total2 = total2 + 7.5
                    Case Is = "3"
                        If v(2, c) = "Za" Or v(2, c) = "Zo" Then
                            total3 = total3 + 6.5
                        Else
                            total3 = total3 + 7.5
                        End If
                    Case Is = "4"
                        If v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And (v(2, c) = "Za" Or v(2, c) = "Zo") Then
                            total4 = total4 + 6
                        ElseIf v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And v(2, c) <> "Za" And v(2, c) <> "Zo" Then
                            total4 = total4 + 5.5
                        ElseIf v(r, c + 1) <> "4" And Day(v(1, c)) = lDay Then
                            total4 = total4 + 10
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) <> "4" And (v(2, c) = "Za" Or v(2, c) = "Zo") Then
                            total4 = total4 + 16
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) <> "4" And v(2, c) <> "Za" And v(2, c) <> "Zo" Then
                            total4 = total4 + 15.5
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) = "Za" Or v(2, c + 3) = "Zo") Then
                            total4 = total4 + 32
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) = "Za" Or v(2, c + 3) = "Zo") Then
                            total4 = total4 + 31
                        End If
                End Select
                total = total + total1 + total2 + total3 + total4
            End If
            If v(r, c) <> "" Then
                Range("B" & r + 4) = Split(Range("B" & r + 4), " ")(0) & " - " & total
                total1 = 0: total2 = 0: total3 = 0: total4 = 0
            End If
        Next c
        total = 0
    Next r
    Application.ScreenUpdating = True
End Sub

 

[Ramballah]

Make the January sheet the active sheet and try this macro. I get slightly different totals for some in column B than what you had. Please double check to see which totals are correct and which totals are not correct and let me know. Perhaps you can figure out how any incorrect totals are being calculated based on your criteria. If we can get this to work properly, it should work on any of your sheets.

Sub CalculateTotals()
    Application.ScreenUpdating = False
    Dim v As Variant, lRow As Long, lCol As Long, r As Long, c As Long, lDay As Long
    Dim total As Double, total1 As Double, total2 As Double, total3 As Double, total4S As Double, total4P As Double
    lCol = Cells(6, Columns.Count).End(xlToLeft).Column '39
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    lDay = Format(CDate(WorksheetFunction.EoMonth(Range("B5"), 0)), "dd")
    v = Range("C5:C" & lRow).Resize(, lCol - 2).Value
    For r = LBound(v) + 2 To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If v(r, c) <> "" Then
                Select Case v(r, c)
                    Case Is = "1"
                        If v(2, c) = "Za" Or v(2, c) = "Zo" Then
                            total1 = total1 + 6
                        Else
                            total1 = total1 + 5.5
                        End If
                    Case Is = "2"
                        total2 = total2 + 7.5
                    Case Is = "3"
                        If v(2, c) = "Za" Or v(2, c) = "Zo" Then
                            total3 = total3 + 6.5
                        Else
                            total3 = total3 + 7.5
                        End If
                    Case Is = "4"
                        If v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And (v(2, c) = "Za" Or v(2, c) = "Zo") Then
                            total4 = total4 + 6
                        ElseIf v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And v(2, c) <> "Za" And v(2, c) <> "Zo" Then
                            total4 = total4 + 5.5
                        ElseIf v(r, c + 1) <> "4" And Day(v(1, c)) = lDay Then
                            total4 = total4 + 10
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) <> "4" And (v(2, c) = "Za" Or v(2, c) = "Zo") Then
                            total4 = total4 + 16
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) <> "4" And v(2, c) <> "Za" And v(2, c) <> "Zo" Then
                            total4 = total4 + 15.5
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) = "Za" Or v(2, c + 3) = "Zo") Then
                            total4 = total4 + 32
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) = "Za" Or v(2, c + 3) = "Zo") Then
                            total4 = total4 + 31
                        End If
                End Select
                total = total + total1 + total2 + total3 + total4
            End If
            If v(r, c) <> "" Then
                Range("B" & r + 4) = Split(Range("B" & r + 4), " ")(0) & " - " & total
                total1 = 0: total2 = 0: total3 = 0: total4 = 0
            End If
        Next c
        total = 0
    Next r
    Application.ScreenUpdating = True
End Sub

  ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) = "Za" Or v(2, c + 3) = "Zo") Then
                            total4 = total4 + 32
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) = "Za" Or v(2, c + 3) = "Zo") Then
                            total4 = total4 + 31

I am unsure what this 32 and 31 is supposed to be? In none of the calculations do I have shifts of 32h or 31h.. I Think this is what gives different totals?
I compared the results of this macro and my own calculations and I get this:

I marked the names yellow that are correct.
With Juanita I come on: 133,5h (maybe cause system thinks to add 10h cause its a 4 on the 31st?)
Michelle: 150h (this is not 149h, as I made an error in calculating it, mentioned it couple posts ago)
Mila: 49,5h (I can only assume the macro thinks its 50h, because it might think that the shift on the 28th/29th is with a sunday. But it starts on a sunday and not end on a sunday. but it makes sense where the 0,5h gets added as if it were to end on a sunday it would indeed be 50h)
Ramon: 134h (I think its the same here but for shift on the 21st and 22nd)
Ruben: 137,5h (this probably is somewhere in that realm too, where the system thinks a shift is 15,5h instead of 16h.

Also from ur code:

                        If v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And (v(2, c) = "Za" Or v(2, c) = "Zo") Then
                            total4 = total4 + 6
                        ElseIf v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And v(2, c) <> "Za" And v(2, c) <> "Zo" Then
                            total4 = total4 + 5.5

Is it saying here, if the first day is a 4, and the day next to it is a 1? If so, this is probably bad as not always is there a "1" next to a 4. It can be blank, or 2 or 3 or just a new pair of 4's..
At the very least this is good progress! I thank you so much for it already!

 

[mumps]

Try:

Sub CalculateTotals()
    Application.ScreenUpdating = False
    Dim v As Variant, lRow As Long, lCol As Long, r As Long, c As Long, lDay As Long
    Dim total As Double, total1 As Double, total2 As Double, total3 As Double, total4S As Double, total4P As Double
    lCol = Cells(6, Columns.Count).End(xlToLeft).Column '39
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    lDay = Format(CDate(WorksheetFunction.EoMonth(Range("B5"), 0)), "dd")
    v = Range("C5:C" & lRow).Resize(, lCol - 2).Value
    For r = LBound(v) + 2 To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If v(r, c) <> "" Then
                Select Case v(r, c)
                    Case Is = "1"
                        If v(2, c) = "Za" Or v(2, c) = "Zo" Then
                            total1 = total1 + 6
                        Else
                            total1 = total1 + 5.5
                        End If
                    Case Is = "2"
                        total2 = total2 + 7.5
                    Case Is = "3"
                        If v(2, c) = "Za" Or v(2, c) = "Zo" Then
                            total3 = total3 + 6.5
                        Else
                            total3 = total3 + 7.5
                        End If
                    Case Is = "4"
                        If v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And (v(2, c) = "Za" Or v(2, c) = "Zo") Then
                            total4 = total4 + 6
                        ElseIf v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And v(2, c) <> "Za" And v(2, c) <> "Zo" Then
                            total4 = total4 + 5.5
                        ElseIf v(r, c + 1) <> "4" And v(r, c - 1) <> "4" And Day(v(1, c)) = lDay Then
                            total4 = total4 + 10
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) <> "4" And (v(2, c + 1) = "Za" Or v(2, c + 1) = "Zo") Then
                            total4 = total4 + 16
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) <> "4" And v(2, c + 1) <> "Za" And v(2, c + 1) <> "Zo" Then
                            total4 = total4 + 15.5
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) = "Za" Or v(2, c + 3) = "Zo") Then
                            total4 = total4 + 16
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) <> "Za" And v(2, c + 3) <> "Zo") Then
                            total4 = total4 + 15.5
                        End If
                End Select
                total = total + total1 + total2 + total3 + total4
            End If
            If v(r, c) <> "" Then
                Range("B" & r + 4) = Split(Range("B" & r + 4), " ")(0) & " - " & total
                total1 = 0: total2 = 0: total3 = 0: total4 = 0
            End If
        Next c
        total = 0
    Next r
    Application.ScreenUpdating = True
End Sub

 

[Ramballah]

Try:

Sub CalculateTotals()
    Application.ScreenUpdating = False
    Dim v As Variant, lRow As Long, lCol As Long, r As Long, c As Long, lDay As Long
    Dim total As Double, total1 As Double, total2 As Double, total3 As Double, total4S As Double, total4P As Double
    lCol = Cells(6, Columns.Count).End(xlToLeft).Column '39
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    lDay = Format(CDate(WorksheetFunction.EoMonth(Range("B5"), 0)), "dd")
    v = Range("C5:C" & lRow).Resize(, lCol - 2).Value
    For r = LBound(v) + 2 To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If v(r, c) <> "" Then
                Select Case v(r, c)
                    Case Is = "1"
                        If v(2, c) = "Za" Or v(2, c) = "Zo" Then
                            total1 = total1 + 6
                        Else
                            total1 = total1 + 5.5
                        End If
                    Case Is = "2"
                        total2 = total2 + 7.5
                    Case Is = "3"
                        If v(2, c) = "Za" Or v(2, c) = "Zo" Then
                            total3 = total3 + 6.5
                        Else
                            total3 = total3 + 7.5
                        End If
                    Case Is = "4"
                        If v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And (v(2, c) = "Za" Or v(2, c) = "Zo") Then
                            total4 = total4 + 6
                        ElseIf v(r, c + 1) <> "4" And Day(v(1, c)) = "1" And v(2, c) <> "Za" And v(2, c) <> "Zo" Then
                            total4 = total4 + 5.5
                        ElseIf v(r, c + 1) <> "4" And v(r, c - 1) <> "4" And Day(v(1, c)) = lDay Then
                            total4 = total4 + 10
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) <> "4" And (v(2, c + 1) = "Za" Or v(2, c + 1) = "Zo") Then
                            total4 = total4 + 16
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) <> "4" And v(2, c + 1) <> "Za" And v(2, c + 1) <> "Zo" Then
                            total4 = total4 + 15.5
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) = "Za" Or v(2, c + 3) = "Zo") Then
                            total4 = total4 + 16
                        ElseIf v(r, c + 1) = "4" And v(r, c + 2) = "4" And v(r, c + 3) = "4" And (v(2, c + 3) <> "Za" And v(2, c + 3) <> "Zo") Then
                            total4 = total4 + 15.5
                        End If
                End Select
                total = total + total1 + total2 + total3 + total4
            End If
            If v(r, c) <> "" Then
                Range("B" & r + 4) = Split(Range("B" & r + 4), " ")(0) & " - " & total
                total1 = 0: total2 = 0: total3 = 0: total4 = 0
            End If
        Next c
        total = 0
    Next r
    Application.ScreenUpdating = True
End Sub

Oh my lord, this works perfect! Wow! Thank you so much!!!
Now my last question would be, do I need to place this macro onto a button, or is it possible to have it automatically run?
For example when I place this VBA on a sheet, I need to run it manually by pressing F5. Is it possible that I don't need to do this, or do I need to use a button?
Anyway, once again thank you so much!

 

[mumps]

You are very welcome. I think that given how your data is organized and entered, it is probably best to place a button on each sheet and assign the macro to it. The macro should work for any month.