Application or Object Defined error in Excel VBA

Question

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

edit the question and show the text of any error messages. Also, it looks like you have a formatting problem in your code example. You should be able to debug or step through your macro, which may give you more to work on. — user1531971
– user1531971, Commented May 3, 2018 at 14:21
Please always include in which line the error occurs, otherwise we have to guess that. — Pᴇʜ
– Pᴇʜ, Commented May 3, 2018 at 14:23
@FoxfireAndBurnsAndBurns no this way as the OP did it, the .SaveAs and .Close would directly interact with the newly added workbook from Workbooks.Add in the with statement. Technically it is the same as using Set wb = Workbooks.Add and using With wb afterwards. — Pᴇʜ
– Pᴇʜ, Commented May 3, 2018 at 14:28
@FoxfireAndBurnsAndBurns With blocks can hold an object reference and avoid needing to declare a local variable for it - I use With New Something all the time =) — Mathieu Guindon
– Mathieu Guindon, Commented May 3, 2018 at 14:45
@Adam please edit your post to include that information, so people can see it without needing to read all the comments. Thanks! — Mathieu Guindon
– Mathieu Guindon, Commented May 3, 2018 at 14:46

CLR · Accepted Answer · 2018-05-03 14:52:56Z

3

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

edited May 3, 2018 at 14:52

answered May 3, 2018 at 14:35

CLR

12.3k1 gold badge13 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Application or Object Defined error in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related