When i manually delete times (to simulate what would happen if an incorrect
number is typed in and had to be manually corrected) and the column number is
9 the following code gives me an error on the line marked. Can anybody tell
me why. Thanks.



Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False


'Column K is column 11
If Target.Column = 11 And Target.Row = 5 Then

TRow = Target.Row
Val1 = Range("G" & TRow).End(xlToRight).Offset(0, -1)
Val2 = Hour(Range("G" & TRow).End(xlToRight).Offset(0, -1))
Val3 = Minute(Range("G" & TRow).End(xlToRight).Offset(0, -1))
Val4 = Second(Range("G" & TRow).End(xlToRight).Offset(0, -1))
val5 = Sheets("timing sheet").Range("c21").Value / 24

If Range("G" & TRow).End(xlToRight).Offset(0, -1).Column < 10 Then

Sheets("a grade").Range("i" & TRow) = 0 'error here

ElseIf Val1 < val5 Then
Sheets("a grade").Range("i" & TRow) = 1000 + Sheets("a grade").Range("j" &
TRow) + (24 - Val2) / 100 + _
(60 - Val3) / 10000 + (60 - Val4) / 1000000
Else
Sheets("a grade").Range("i" & TRow) = 4000 + Sheets("a grade").Range("j" &
TRow) + (24 - Val2) / 100 + _
(60 - Val3) / 10000 + (60 - Val4) / 1000000

End If
End If


Application.ScreenUpdating = True
End Sub

not sure if it is important but when you delete times you delete 2 cells
together. columns 11 and 12. Thought I'd better mention this in case it is
effecting something.

"NDBC" wrote:

When i manually delete times (to simulate what would happen if an incorrect
number is typed in and had to be manually corrected) and the column number is
9 the following code gives me an error on the line marked. Can anybody tell
me why. Thanks.



Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False


'Column K is column 11
If Target.Column = 11 And Target.Row = 5 Then

TRow = Target.Row
Val1 = Range("G" & TRow).End(xlToRight).Offset(0, -1)
Val2 = Hour(Range("G" & TRow).End(xlToRight).Offset(0, -1))
Val3 = Minute(Range("G" & TRow).End(xlToRight).Offset(0, -1))
Val4 = Second(Range("G" & TRow).End(xlToRight).Offset(0, -1))
val5 = Sheets("timing sheet").Range("c21").Value / 24

If Range("G" & TRow).End(xlToRight).Offset(0, -1).Column < 10 Then

Sheets("a grade").Range("i" & TRow) = 0 'error here

ElseIf Val1 < val5 Then
Sheets("a grade").Range("i" & TRow) = 1000 + Sheets("a grade").Range("j" &
TRow) + (24 - Val2) / 100 + _
(60 - Val3) / 10000 + (60 - Val4) / 1000000
Else
Sheets("a grade").Range("i" & TRow) = 4000 + Sheets("a grade").Range("j" &
TRow) + (24 - Val2) / 100 + _
(60 - Val3) / 10000 + (60 - Val4) / 1000000

End If
End If


Application.ScreenUpdating = True
End Sub

just worked it out. The sheet was protected.

"NDBC" wrote:

not sure if it is important but when you delete times you delete 2 cells
together. columns 11 and 12. Thought I'd better mention this in case it is
effecting something.

"NDBC" wrote:

When i manually delete times (to simulate what would happen if an incorrect
number is typed in and had to be manually corrected) and the column number is
9 the following code gives me an error on the line marked. Can anybody tell
me why. Thanks.



Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False


'Column K is column 11
If Target.Column = 11 And Target.Row = 5 Then

TRow = Target.Row
Val1 = Range("G" & TRow).End(xlToRight).Offset(0, -1)
Val2 = Hour(Range("G" & TRow).End(xlToRight).Offset(0, -1))
Val3 = Minute(Range("G" & TRow).End(xlToRight).Offset(0, -1))
Val4 = Second(Range("G" & TRow).End(xlToRight).Offset(0, -1))
val5 = Sheets("timing sheet").Range("c21").Value / 24

If Range("G" & TRow).End(xlToRight).Offset(0, -1).Column < 10 Then

Sheets("a grade").Range("i" & TRow) = 0 'error here

ElseIf Val1 < val5 Then
Sheets("a grade").Range("i" & TRow) = 1000 + Sheets("a grade").Range("j" &
TRow) + (24 - Val2) / 100 + _
(60 - Val3) / 10000 + (60 - Val4) / 1000000
Else
Sheets("a grade").Range("i" & TRow) = 4000 + Sheets("a grade").Range("j" &
TRow) + (24 - Val2) / 100 + _
(60 - Val3) / 10000 + (60 - Val4) / 1000000

End If
End If


Application.ScreenUpdating = True
End Sub