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]

The formula that c40 needs to be is =if(and(or($C$40=0.7,$C$40=0.8)*or($B$40=0.9,$B$40=1)),”error”,””)

 

[Fluff]

I will ask one further time

For me to be able to offer any further help, you need to explain clearly & concisely EXACTLY what you need.

Your above formula is completely different from what you have said before.

 

[EwEn999]

I apologise for not being entirely clear in my request. Without your help I wouldn’t be able to complete the project.


What I would like is the above formula to be applied to the following ranges in VBA C28:H28, C32:H32, C36:H36, C40:H40, C44:H44 where if true a message box appears in the following format

[COLOR=#454545][FONT='inherit'][FONT='inherit']MsgBox [insert officer’s name]  & " Has been scheduled a night shift followed by a day shift on [insert day]" & Msg & vbNewLine & "Please Rectify." & vbNewLine & _[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT='inherit'][FONT='inherit']           "Press OK to acknowledge.", vbExclamation + vbOKOnly, "Error"
[/FONT][/FONT][/COLOR]

p.s. I only discovered VBA at the beginning of the month so it’s a very steep learning curve.

 

[Fluff]

try

Sub EwEn999()
   Dim i As Long, j As Long
   Dim Msg As String
   
   For i = 28 To 44 Step 4
      If (Range("B" & i) = 0.9 Or Range("B" & i) = 1) And (Range("C" & i) = 0.7 Or Range("C" & i) = 0.8) Then
         MsgBox Range("A" & i) & " Has been scheduled a night shift followed by a day shift on Monday/Tuesday" & Msg & vbNewLine & "Please Rectify." & vbNewLine & _
            "Press OK to acknowledge.", vbExclamation + vbOKOnly, "Error"
         End If
   Next i
End Sub