CODEWITHSUNDEEP
vbaexcel
Adam
Adam

Reputation: 57

Application or Object Defined error in Excel VBA

I am getting an error when i input this code into my macro, with the error message stating that its an object defined error. Can i know where the error is coming from?

Option Explicit

Public Const strSA As String = "C:\Users\kentan\Desktop\Managed Fund "

Sub iris()
Dim i As Long
With ActiveSheet
    With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1))
        .Sort key1:=.Columns(1), order1:=xlAscending , _
              key2:=.Columns(2), order2:=xlAscending , _
              Header:=xlYes, MatchCase:=False, _
              Orientation:=xlTopToBottom, SortMethod:=xlStroke
    End With

    For i = 2 To .Rows.Count
        If LCase(.Cells(i, "A").Value2) = LCase(.Cells(i - 1, "A").Value2) And _
           LCase(.Cells(i, "A").Value2) <> LCase(.Cells(i + 1, "A").Value2) Then
            newiris .Cells(i, "A").Value2, .Cells(i, "B").Value2
        End If
    Next i
End With
End Sub

Sub newiris(nm As String, nfo As String)
Application.DisplayAlerts = false
With Workbooks.Add
    Do While .Worksheets.Count > 1: .Worksheets(2).Delete: Loop
    .Worksheets(1).Cells(1, "A").Resize(1, 2) = Array(nm, nfo)
    .SaveAs filename:=strSA & nm, FileFormat:=xlOpenXMLWorkbook
    .Close savechanges:=False
End With
Application.DisplayAlerts = true
End Sub

Upvotes: 1

Views: 51

Answers (1)

CLR
CLR

Reputation: 12279

Your issue is that you're reading every row in the entire sheet:

For i = 2 To.Rows.Count

You're then testing the value of a cell on that row and comparing it to the row below:

LCase(.Cells(i + 1, "A").Value2)

If you're on the last row (1,048,576) - you can't compare that to the next row (1,048,577) as that row doesn't exist.

Try using:

For i = 2 To .Rows.Count-1

Or better yet, get it to only scan the rows with data:

For i = 2 To.Cells(.Rows.Count, 1).End(xlUp).Row

Upvotes: 3

Related Questions