Vba formula help

E

EwEn999

New Member
Joined
Dec 3, 2018
Messages
27
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

Code: 
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]
 
Sort by date Sort by votes
Code: 
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.
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
Can you please show a sample of your data & explain exactly what you need?
 
Upvote 0
Fluff said:
Can you please show a sample of your data & explain exactly what you need?
Click to expand...

Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/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"]1[/TD]
[TD="align: center"]Site 1[/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"]2[/TD]
[TD="align: center"]Officer 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Officer 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Officer 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Officer 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Officer 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="width: 500"]
<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"]24[/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"]25[/TD]
[TD="align: center"]S1[/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"]S2[/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"]S3[/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"]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][/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"][/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"][/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"][/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"][/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"]44[/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]
</tbody>[/TABLE]

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.
 
Upvote 0
You have not show any data!
Where are the numbers that need to be checked?
 
Upvote 0
EwEn999 said:
Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/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"]1[/TD]
[TD="align: center"]Site 1[/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"]2[/TD]
[TD="align: center"]Officer 1[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Officer 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Trg(D)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Officer 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Officer 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Officer 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="width: 500"]
<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"]24[/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"]25[/TD]
[TD="align: center"]S1[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"]S2[/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"]S3[/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"]Officer 1[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]S1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]S2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]S3[/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"]Officer 2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1.5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]33[/TD]
[TD="align: center"]S1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD="align: center"]S2[/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"]S3[/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"]Officer 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]37[/TD]
[TD="align: center"]S1[/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"]1[/TD]
[/TR]
[TR]
[TD="align: center"]38[/TD]
[TD="align: center"]S2[/TD]
[TD="align: center"]0.9[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]39[/TD]
[TD="align: center"]S3[/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"]Officer 4[/TD]
[TD="align: center"]0.9[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]41[/TD]
[TD="align: center"]S1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]42[/TD]
[TD="align: center"]S2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]43[/TD]
[TD="align: center"]S3[/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"]44[/TD]
[TD="align: center"]Officer 5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Speeding up VBA
Replies
9
Views
272
jvoss
J
willow1985
  • Solved
VBA clear contents of multiple ranges.
Replies
5
Views
285
willow1985
willow1985
P
  • Question Question
VBA copy pastespecial has become slow
Replies
8
Views
1K
pwylie007
P
I
  • Question Question
Unable to run VBA code
Replies
6
Views
165
RoryA
RoryA
S
  • Solved
Copying Formulas with Formula.R1C1 Results in Rows and Columns Incrementing by 2
Replies
5
Views
610
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top