0

It's been a long time since I used VBA, or even excel, but for reasons to do with my work and tedious excel editing, I decided I'd jass stuff up by making it automatic. I have a spreadsheet with a table that calculates wages of Employees (censored employee names and most of the table edited out for obvious reasons):

EDITED FOR PRIVACY REASOSN

The table is based on a point system, where the future boss is going to put a number of points from 1 to 3 in each column (with the exception of the absence column) based on how well the employee does that day. Then the spreadsheet sums up the total points in the "Total" column to the right. Then the employee is graded from A+ to C based on these points. That's my part. What I decided to do is write a simple vba code to automatically do that (there are way more employees than you see on that picture,so dont call me lazy!). So here is what is started with:

Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveSheet.Range("P7").Value >= 10 Then   'Total points value
        ActiveSheet.Range("Q7").Value = "A+"      'change value of the grade
    ElseIf ActiveSheet.Range("P7").Value >= 8 Then
        ActiveSheet.Range("Q7").Value = "A"
    ElseIf ActiveSheet.Range("P7").Value >= 6 Then
        ActiveSheet.Range("Q7").Value = "B+"
    ElseIf ActiveSheet.Range("P7").Value >= 4 Then
        ActiveSheet.Range("Q7").Value = "B-"
    Else
        ActiveSheet.Range("Q7").Value = "C"
    End If
End Sub

It seems pretty simple to me, but i have an issue that every time it runs i get this error:

Method "Range' of object "_Worksheet" failed

And that's not even it. If i click "end" when the error prompt comes up, it works! works perfectly the way I wanted it to!

So can someone help with the error, point to me whats wrong in my code And even more can someone PLEASE explain to me why I'm getting that error every time that code executes but the code STILL works. How is this possible??

Improve this question
6
  • So when it fails which line does it highlight? You are using worksheet based event to do this while just referring to one cell in the code (P7) to update (Q7). Are you using one sheet per employee? Commented Nov 5, 2017 at 15:10
  • 1
    Why not use a formula? This is fairly simple INDEX(MATCH()). Commented Nov 5, 2017 at 15:11
  • As to the code try changing ActiveSheet to Me. Commented Nov 5, 2017 at 15:12
  • @ShrivallabhaRedji It highlights the first if statement. never mind the fact that I'm doing this to one cell only, i'll change that later. First i need to make sure the code actually works Commented Nov 5, 2017 at 15:14
  • @ScotCraner Yes i want to use code, and changing it to "Me" didn't work. I'm honestly as puzzled as you guys. Commented Nov 5, 2017 at 15:16

1 Answer 1

Reset to default
1

I could not post the code in comment but following code works without error and does what you intend to do.

The glitch seems to arise from the issue that the cell being checked (Target) and cell being changed (Q7) are in conflict as they are firing simultaneously.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo eosub
Application.EnableEvents = False
    If ActiveSheet.Range("P7").Value >= 10 Then   'Total points value
        ActiveSheet.Range("Q7").Value = "A+"      'change value of the grade
    ElseIf ActiveSheet.Range("P7").Value >= 8 Then
        ActiveSheet.Range("Q7").Value = "A"
    ElseIf ActiveSheet.Range("P7").Value >= 6 Then
        ActiveSheet.Range("Q7").Value = "B+"
    ElseIf ActiveSheet.Range("P7").Value >= 4 Then
        ActiveSheet.Range("Q7").Value = "B-"
    Else
        ActiveSheet.Range("Q7").Value = "C"
    End If
eosub:
Application.EnableEvents = True
End Sub

However, if you are manually filling the column P then this is how it should be:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo eosub
Application.EnableEvents = False
If Target.Column = 16 Then
    If Target.Value >= 10 Then   'Total points value
        Target.Offset(0, 1).Value = "A+"     'change value of the grade
    ElseIf Target.Value >= 8 Then
        Target.Offset(0, 1).Value = "A"
    ElseIf Target.Value >= 6 Then
        Target.Offset(0, 1).Value = "B+"
    ElseIf Target.Value >= 4 Then
        Target.Offset(0, 1).Value = "B-"
    Else
        Target.Offset(0, 1).Value = "C"
    End If
End If
eosub:
Application.EnableEvents = True
End Sub

However, the simplest would be to use formula Setup suggested by Scott Craner.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I don't understand how this works really, but it does! However i don't like to just copy stuff from other people without actually knowing what I am doing. I do recognize what's wrong now though, so thank you very much for that.
Very good so it works! What is it that you don't understand here?
Oh no it;s just that it has been a while since i coded VBA so it'l take me a bit of time to get used to it. Thanks anyway

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.