Application-Defined or Object-Defined Error

[Chinpunk]

Evening ladies & gents,

I got this piece of code used to run fine but I started getting the above message.

Sub DetermineRisk()

Dim i As Integer
Dim MyLastRow As Long
Dim MyVar As Double
Dim MyChange As Double
ThisWorkbook.Activate
For i = 4 To ActiveWorkbook.Worksheets.Count
    Sheets(i).Activate
    MyLastRow = Cells(Application.Rows.Count, 16).End(xlUp).Row
    MyVar = Cells(MyLastRow, 16).Offset(-1, 0)
    MyChange = Cells(MyLastRow, 16).Offset(0, 0)
    
    If Abs(MyVar) > 25000000 And Abs(MyChange) > 0.1 Then
        ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.ColorIndex = 3
        
    Else
        ActiveWorkbook.Sheets(ActiveSheet.Name).Tab.ColorIndex = 4
    End If
Next i

End Sub

The colored rows are the ones that have the issue.
Im guessing it has something to do with the Cells reference.
Any assistance would be much appreciated.

Cheers

[Mordred]

Why bother with the

.Offset(0,0)

I don't really understand that line of code. Couldn't it just be

MyChange = Cells(MyLastRow,16)

At any rate, the following didn't give me any errors:

Sub DetermineRisk()

Dim i As Integer
Dim MyLastRow As Long
Dim MyVar As Double
Dim MyChange As Double
ThisWorkbook.Activate
For i = 4 To ActiveWorkbook.Worksheets.Count
    With Sheets(i)
        MyLastRow = Sheets(i).Cells(Sheets(i).Rows.Count, 16).End(xlUp).Row
        MyVar = Sheets(i).Cells(MyLastRow, 16).Offset(-1, 0)
        MyChange = Sheets(i).Cells(MyLastRow, 16).Offset(0, 0)
    
        If Abs(MyVar) > 25000000 And Abs(MyChange) > 0.1 Then
            Sheets(i).Tab.ColorIndex = 3
        
        Else
            Sheets(i).Tab.ColorIndex = 4
        End If
    End With
Next i

End Sub

[Chinpunk]

Yes, Mordred indeed that line of code does not make sense.
I copied pasted from the above line thinking that its one cell down in the same column.
I ran the script again seems to be working.

Thank you for your time.

[Sbarro79]

Hi Chinpunk

I tested your code and found out that the problem occurs when MyLastRow variable = 1, because then MyVar tries to refer to row No.... 0, which obviously doesn't exist.
I think this can be fixed using this:

On Error Resume Next
MyVar = Sheets(i).Cells(MyLastRow, 16).Offset(-1, 0)
        MyChange = Sheets(i).Cells(MyLastRow, 16).Offset(0, 0)
On Error GoTo 0

[Chinpunk]

Hey Sbarro,

Thanks for your reply.
When i was hovering my mouse over the MyLastRow I saw the =1 value.
I will give your code a try in case that error shows up again.
By the way im using excel 2010 version.

Thank you for you time.