Anothe hopefully simple vba fix

[123rickfear]

Hi all,

I have inherited a file that I have made a small number of changes to.

Now, when I run the macro, I keep getting the message box appear saying "please check data in row 10".

Sorry it's a bit long.

Thanks in advance.

Sub validation(errorcheck)
 
  errorcheck = False
  RecordCount = Range("recordcount").Value
  totalerror = True
  
  'errorcheck
  For x = 1 To RecordCount
    On Error GoTo line1
    totalcheck = Range("data").Cells(x, 26) * 2
    If x = RecordCount Then totalerror = False
    
  Next x
  
  'No personnel number
  For x = 1 To RecordCount
    If Range("data").Cells(x, 26) > 0 And Range("data").Cells(x, 1) = "" Then
      MsgBox "Employee with absence missing employee number. Row " & x + 9
      errorcheck = True
      GoTo line1
    End If
  Next x
  
  'check number exists
  For x = 1 To RecordCount
    If Range("data").Cells(x, 1) <> "" Then
      Range("activeee") = Int(Range("data").Cells(x, 1))
      DoEvents
      If Range("nocheck") < 1 Then
        MsgBox "Employee number not recognised. Row " & x + 9
        errorcheck = True
        GoTo line1
      End If
    End If
  Next x
  
  'contracted hours are correct
  For x = 1 To RecordCount
    If Range("data").Cells(x, 1) <> "" Then
      Range("activeee") = Int(Range("data").Cells(x, 1))
      DoEvents
      If Range("data").Cells(x, 25) <> Range("eehours") Then
        MsgBox "Contracted hours do not match system. Row " & x + 9
        errorcheck = True
        GoTo line1
      End If
    End If
  Next x
  
  'employee is a leaver
  For x = 1 To RecordCount
    If Range("data").Cells(x, 1) <> "" Then
      Range("activeee") = Int(Range("data").Cells(x, 1))
      DoEvents
      If Range("eestatus") <> "Active" Then
        MsgBox "Employee is a leaver. Row " & x + 9
        errorcheck = True
        GoTo line1
      End If
    End If
  Next x
  
  'overtime been used where hours exceed 38
  'should this be a check?  Shouldnt all hours be entered as additional
  
  'Absence hours no absence reason
  For x = 1 To RecordCount
    If Range("data").Cells(x, 1) = "" Then GoTo line3
    
    For y = 1 To 7
      test2 = 5 + (y - 1) * 3
      If Range("data").Cells(x, 5 + (y - 1) * 3) > 0 Then
        test = 6 + (y - 1) * 3
        If Range("data").Cells(x, 6 + (y - 1) * 3) = "" Then
          MsgBox "Employee absence hours, no reason. Row " & x + 9
          errorcheck = True
          GoTo line1
        End If
      End If
    Next y
      
line3:
  Next x
  
  'Absence hours greater than working hours for a day
  For x = 1 To RecordCount
    If Range("data").Cells(x, 1) = "" Then GoTo line2
    For y = 1 To 7
      
      If Range("data").Cells(x, 6 + (y - 1) * 3) = "Additional Hours" Then GoTo line4
      If Range("data").Cells(x, 6 + (y - 1) * 3) = "Overtime @ 1.5" Then GoTo line4
      If Range("data").Cells(x, 6 + (y - 1) * 3) = "Bank Holiday - Worked" Then GoTo line4
      
      If Range("data").Cells(x, 5 + (y - 1) * 3) = "" Then GoTo line2
        If Range("data").Cells(x, 4 + ((y - 1) * 3)) < Range("data").Cells(x, 5 + ((y - 1) * 3)) Then
        MsgBox "Employees normal hours less than absence hours. Row " & x + 9
        errorcheck = True
        GoTo line1
      End If
line4:
    Next y
line2:
  Next x
  'Check that a date is present for all records
  For x = 1 To RecordCount
    If Range("data").Cells(x, 1) = "" Then GoTo line5
    If Range("data").Cells(x, 3) = "" Then
      MsgBox "Employee with absence, no date entered. Row " & x + 9
      errorcheck = True
      GoTo line1
    End If
line5:
  Next x
line1:
  'Will skip conversion if there is a problem with the total column on any record
  If totalerror = True Then
    MsgBox "Please check data in row " & x + 9
    errorcheck = True
  End If
End Sub

 

[wideboydixon]

Looking at the code, it would appear that it's always going to "drop through" to the line1: label and display an error message because there's no "Exit Sub" anywhere. So your problem is one of two things:

1. An error is being thrown in the code somewhere which is sending execution to this line (as per the "On Error Goto line1" statement)
2. The code is just "dropping through" to the line1: label and displaying the error

For the first instance, you could remove the "On Error GoTo line1" statement in the code which would then break on the offending line
For the second instance, you could add an "Exit Sub" line just above the line1: label

WBD