VBA code - error after 20 rows

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
I never click on links.
Please post your code here in the forum for us to see.
 
Upvote 0
My Aswer Is This said:
I never click on links.
Please post your code here in the forum for us to see.
Click to expand...

Sorry, of course that makes sense.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)'On Error GoTo ProcError
Application.ScreenUpdating = False


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'FRI End Time - 18 y/o check
If Target.Column = 5 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKA" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If FRI END - SAT START is less than 11h rest Then ERROR
    If Range("BJT" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'SAT Start Time - 11H check - Against Previous Day
If Target.Column = 12 Then
    'If Less than FRI END - SAT START is less than 11h rest Then ERROR
    If Range("BJT" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'SAT End Time - 18 y/o check
If Target.Column = 13 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKB" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than SAT END - SUN START is less than 11h rest Then ERROR
    If Range("BJU" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'SUN Start Time - 11H check - Against Previous Day
If Target.Column = 20 Then
    'If Less than SAT END - SUN START is less than 11h rest Then ERROR
    If Range("BJU" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'SUN End Time - 18 y/o check
If Target.Column = 21 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKC" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than SUN END - MON START is less than 11h rest Then ERROR
    If Range("BJV" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
'MON Start Time - 11H check - Against Previous Day
If Target.Column = 28 Then
    'If Less than SUN END - MON START is less than 11h rest Then ERROR
    If Range("BJV" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'MON End Time - 18 y/o check
If Target.Column = 29 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKD" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than MON END - TUE START is less than 11h rest Then ERROR
    If Range("BJW" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'TUE Start Time - 11H check - Against Previous Day
If Target.Column = 36 Then
    'If Less than MON END - TUE START is less than 11h rest Then ERROR
    If Range("BJW" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'TUE End Time - 18 y/o check
If Target.Column = 37 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKE" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than TUE END - WED START is less than 11h rest Then ERROR
    If Range("BJX" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'WED Start Time - 11H check - Against Previous Day
If Target.Column = 44 Then
    'If Less than TUE END - WED START is less than 11h rest Then ERROR
    If Range("BJX" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'WED End Time - 18y/o check
If Target.Column = 45 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKF" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than WED END - THU START is less than 11h rest Then ERROR
    If Range("BJY" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'THU Start Time - 11H check - Against Previous Day
If Target.Column = 52 Then
    'If Less than WED END - THU START is less than 11h rest Then ERROR
    If Range("BJY" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'THU End Time - 18y/o check
If Target.Column = 53 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKG" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ProcError: MsgBox "ERROR!"
Application.ScreenUpdating = True
End Sub
 
Upvote 0
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
It is giving an error message every time a change is made because there is nothing telling it to exit the code before your error handling section. Add an "Exit Sub" line there.

This is how you will want to end you code (under your ''''''''... line):
Code: 
Application.ScreenUpdating = True
Exit Sub
 
ProcError: MsgBox "ERROR!"
Application.ScreenUpdating = True
 
End Sub
 
Upvote 0
I'll try it tomorrow, thank you!

Also, is there any way to shorten the code?
I mean, I am basically repeating 3 commands over and over for different columns?
I couldn't get it to work using CASE and it would be super helpful if someone can help me out with that to both make it more manageable and allow me to add more code without worrying about reaching the max commands in a single macro. (Yes, I've got other sheets with that problem, but they're not related to this thread)

Once again - thank you!
 
Last edited:
Upvote 0
If you have sections that repeat, meaning they follow the SAME logic, then you can use loops to loop through one section of code multiple times.
Basically, I see two main ways to do these kind of loops:

1. If there is a consistent "jump" in the values you need to check, you can use a loop like this (this example checks columns 3,6,9,12,15):
Code: 
Dim c as Integer
For c = 3 to 15 Step 3
    If Target.Column = c Then
        ...
    End If
Next c

2. If the sections you need to loop through are not "consistent jumps", you can store the values in an array, and loop through the array, like this:
Code: 
    Dim cols As Variant
    Dim i As Long
    cols = Array(5, 8, 17, 29, 36)
    For i = LBound(cols) To UBound(cols)
        If Target.Column = cols(i) Then
            ....
        End If
    Next i
 
Upvote 0
Hello!

My code still doesn't work as intended.

Just want to re-iterate that it trigers on every column and row (even though I've tried specifying which ones to tiger it) and it doesn't work past ...like row 30 or something.
Every time I make a change it goes through itself and exits (if I put the above suggested exit before the end of the macro or if that's not there it'll just always proc the error.

Help please.
 
Upvote 0
Please post your exact variation of your latest code attempt, so we can see it after you made the adjustments to it.
 
Last edited:
Upvote 0
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)'On Error GoTo ProcError
Application.ScreenUpdating = False


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'FRI End Time - 18 y/o check
If Target.Column = 5 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKA" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If FRI END - SAT START is less than 11h rest Then ERROR
    If Range("BJT" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'SAT Start Time - 11H check - Against Previous Day
If Target.Column = 12 Then
    'If Less than FRI END - SAT START is less than 11h rest Then ERROR
    If Range("BJT" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'SAT End Time - 18 y/o check
If Target.Column = 13 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKB" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than SAT END - SUN START is less than 11h rest Then ERROR
    If Range("BJU" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'SUN Start Time - 11H check - Against Previous Day
If Target.Column = 20 Then
    'If Less than SAT END - SUN START is less than 11h rest Then ERROR
    If Range("BJU" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'SUN End Time - 18 y/o check
If Target.Column = 21 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKC" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than SUN END - MON START is less than 11h rest Then ERROR
    If Range("BJV" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
'MON Start Time - 11H check - Against Previous Day
If Target.Column = 28 Then
    'If Less than SUN END - MON START is less than 11h rest Then ERROR
    If Range("BJV" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'MON End Time - 18 y/o check
If Target.Column = 29 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKD" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than MON END - TUE START is less than 11h rest Then ERROR
    If Range("BJW" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'TUE Start Time - 11H check - Against Previous Day
If Target.Column = 36 Then
    'If Less than MON END - TUE START is less than 11h rest Then ERROR
    If Range("BJW" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'TUE End Time - 18 y/o check
If Target.Column = 37 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKE" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than TUE END - WED START is less than 11h rest Then ERROR
    If Range("BJX" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'WED Start Time - 11H check - Against Previous Day
If Target.Column = 44 Then
    'If Less than TUE END - WED START is less than 11h rest Then ERROR
    If Range("BJX" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'WED End Time - 18y/o check
If Target.Column = 45 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKF" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
    'If Less than WED END - THU START is less than 11h rest Then ERROR
    If Range("BJY" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'THU Start Time - 11H check - Against Previous Day
If Target.Column = 52 Then
    'If Less than WED END - THU START is less than 11h rest Then ERROR
    If Range("BJY" & Target.Row).Value = 0 Then
        MsgBox "This staff member needs more rest!", vbExclamation, "ERROR"
    End If
End If


'THU End Time - 18y/o check
If Target.Column = 53 Then
    'If Under 18 and working past 23:00 Then ERROR
    If Range("BJR" & Target.Row).Value = 0 Then
        If Range("BKG" & Target.Row).Value = 1 Then
            MsgBox "This staff member is under 18 and cannot work past 23:00!", vbExclamation, "ERROR"
        End If
    End If
End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


ProcError: MsgBox "ERROR! Please notify Peter!"
Application.ScreenUpdating = True


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

zookeepertx
  • Solved
Trying (unsuccessfully) to pull a date from the middle of a string
2 3
Replies
21
Views
324
zookeepertx
zookeepertx
B
  • Solved
Need code that converts colors from the RGB code to the Natural Color System (NCS)
Replies
6
Views
189
PeteWright
PeteWright
M
  • Solved
VBA - For Cycle with "On Error" not working on the second iteration
Replies
9
Views
129
Joe4
Joe4
Z
  • Solved
Code working within a file, giving Error 448 within another file
Replies
2
Views
102
zankzank
Z
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
84
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,713
Messages
6,174,039
Members
452,542
Latest member
Bricklin

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