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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"]Sun[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"]Officer 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]Officer 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]35[/TD]
[TD="align: center"]Officer 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]36[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]38[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]39[/TD]
[TD="align: center"]Officer 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]41[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]42[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]43[/TD]
[TD="align: center"]Officer 5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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