Vba formula help

[EwEn999]

Hi all, I'm looking for help in trying to achieve the following. If any of the values in a range is >1, then use the corresponding name from column A.

The comments in the code below will hopefully explain what I'm hoping for.

I've also created a table for reference.

Thanks in advance

Sub ErrorMsg1()

    Dim OfficersName As String
    Dim Totals As Integer
    
    'if any value in the range b27:h27 is >1, use the officer's name in a27 in the corresponding msgbox
    'if any value in the range b31:h31 is >1, use the officer's name in a31 in the corresponding msgbox
    'if any value in the range b35:h35 is >1, use the officer's name in a35 in the corresponding msgbox
    'if any value in the range b39:h39 is >1, use the officer's name in a39 in the corresponding msgbox
    'if any value in the range b43:h43 is >1, use the officer's name in a43 in the corresponding msgbox
        
    Totals = Range("B27").Value
    OfficersName = Range("A27")
    
    If Totals > 1 Then
        MsgBox OfficersName & " Has been scheduled on 2 or more sites." & vbNewLine & "Please Rectify." & vbNewLine & _
        "Press OK to acknowledge.", vbExclamation + vbOKOnly, "Error"
    Else
        Exit Sub
    End If
    
End Sub

	A	B	C	D	E	F	G	H
		Mon	Tue	Wed	Thu	Fri	Sat	Sun
24
25
26
27	Officer 1
28
29
30
31	Officer 2
32
33
34
35	Officer 3
36
37
38
39	Officer 4
40
41
42
43	Officer 5

<tbody>
</tbody>

 

[EwEn999]

Sub ErrorMsg2()   Dim i As Long, j As Long
   Dim Msg As String
   
   For i = 28 To 44 Step 4
      If Range("B" & i) >= 0.9 And Range("B" & i) <= 1 And Range("C" & i) >= 0.7 And Range("C" & i) <= 0.8 Then
         If Application.CountIf(Range("B" & i).Resize(, 7), ">1") Then
            For j = 1 To 7
               If Cells(i, j) > 1 Then
                  If Msg = "" Then Msg = Cells(24, j) Else Msg = Msg & ", " & Cells(24, j)
               End If
            Next j
            MsgBox Range("A" & i) & " Has been scheduled a night shift followed by a day shift on " & Msg & vbNewLine & "Please Rectify." & vbNewLine & _
           "Press OK to acknowledge.", vbExclamation + vbOKOnly, "Error"
           Msg = ""
         End If
      End If
   Next i
End Sub

This code doesn't want to work.

 

[Fluff]

This code doesn't want to work.

In what way?
Also what does your data like?

 

[EwEn999]

When I ran the sub in break mode, it appeared to only run through about 6 times rather than the expected 30.
When I entered B28=1 and C28=0.8, it just ran without picking up the error. I hope I’ve explained myself sufficiently.

 

[Fluff]

Can you please show a sample of your data & explain exactly what you need?

 

[EwEn999]

Can you please show a sample of your data & explain exactly what you need?

Table 1

	A	B	C	D	E	F	G	H
1	Site 1	Mon	Tue	Wed	Thu	Fri	Sat	Sun
2	Officer 1
3	Officer 2
4	Officer 3
5	Officer 4
6	Officer 5

<tbody>
</tbody>

Table 2

	A	B	C	D	E	F	G	H
24		Mon	Tue	Wed	Thu	Fri	Sat	Sun
25	S1
26	S2
27	S3
28	Officer 1
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

<tbody>
</tbody>

Table 1 is repeated 3 times. Table 1 is designed to be used by the end user for data input.
The type of data which will be added are 'D' = day shift, 'N' = night shift, 'H' = holiday etc.

The formulas in cells (B25:H25) is =(IF(B2="D",0.8,0))+(IF(B2="N",1,0))+(IF(B2="H",0.55,0))+(IF(B2="Trg(D)",0.7,0))+(IF(B2="Trg(N)",0.9,0))+(IF(B2="S",0.6,0))
The above formula is repeated in every cell for each site and every officer, apart from rows 28, 32, 36, 40 & 44

The formula used in cells (B28:H28) is =sum(B25:B27). This is repeated on rows 32, 36, 40 & 44

The purpose of the first macro is ensure no officer is accidentally scheduled for 2 shifts on the same day.

The purpose of the second macro is ensure no officer is accidentally scheduled for a night shift followed by a day shift.

This is just a test workbook. The final workbook will have 15+ sites with 50+ officers.

Hopefully, I have provided you enough material for you to better understand what I'm trying to achieve.

 

[Fluff]

You have not show any data!
Where are the numbers that need to be checked?

 

[EwEn999]

Table 1

	A	B	C	D	E	F	G	H
1	Site 1	Mon	Tue	Wed	Thu	Fri	Sat	Sun
2	Officer 1	D	D	N	N
3	Officer 2					Trg(D)
4	Officer 3			D	D	N	N
5	Officer 4							N
6	Officer 5					D	D	N

<tbody>
</tbody>

Table 2

	A	B	C	D	E	F	G	H
24		Mon	Tue	Wed	Thu	Fri	Sat	Sun
25	S1	0.8	0.8	1	1
26	S2
27	S3
28	Officer 1	0.8	0.8	1	1
29	S1					0.7
30	S2					0.8
31	S3
32	Officer 2					1.5
33	S1			0.8	0.8	1	1
34	S2
35	S3
36	Officer 3			0.8	0.8	1	1
37	S1							1
38	S2	0.9	0.8					1
39	S3
40	Officer 4	0.9	0.8					2
41	S1					0.8	0.8	1
42	S2				1
43	S3
44	Officer 5				1	0.8	0.8	1

<tbody>
</tbody>

Ive added what a normal week might look like.

The error that macro1 should test for if ran should pick up errors in cells F32 and H40.
The error that macro 2 should test for of ran should pick up errors in C40 and F44

 

[Fluff]

Firstly do you want 1 macro or 2?
Also
1) why would C40 cause an error?
2) why would B28 not cause on error?
3) You said to look in Col C & D, so why would F44 cause an error?
For me to be able to offer any further help, you need to explain clearly & concisely EXACTLY what you need.

 

[EwEn999]

There are 2 logical test, so 2 macros please.
1) the error in C40 is caused by the test in reply #6 .
2) B28 cannot be in error as it = 0.8 and it is in the first column.
3)

the purpose of the first macro is to check that any of the following ranges doesn’t exceed 1.
B28:H28, B32:H32, B36:H36, B40:H40, B44:B44

the purpose of the second macro is to check that the cell immediately to the left of the current cell is 0.9<=x<=1 and the selected cell is 0.7<=x<=0.8. It applies to the following ranges: C28:H28, C32:H32, C36:H36, C40:H40, C44:H44.

Apologies if if I haven’t been clear and concise in the past. I really appreciate all that you have done so far.

 

[Fluff]

C40 is 0.8 so does not fail the test of >= 0.7 & <= 0.8
B28 is 0.8 & is therefore <0.9 therefore fails the test.