CODEWITHSUNDEEP
vbaexcel
Huỳnh Tùng
Huỳnh Tùng

Reputation: 79

Application-defined or Object-defined error in Excel code

My code below is used to open an order report file and a list of keywords file then use AutoFilter to filter required data in order report file by a list of keywords then copy to another worksheet.

My problem is that when this code runs:

OrderCount = wsOrder.Range("A2", wsOrder.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells.Count

...the error Application-defined or Object-defined error occurrs, but only on certain order report files. I have 2 exactly layout, criteria workbook only data is different, yet one file is able to be run but the other does not.

It runs fine on one workbook (below) but nor the other. img

img

Public Sub btn1_Click()
    Dim i As Double, N As Double, strKeyWord As String, myCount As Integer
    Dim OrderCount As Integer, SubTotal As Range, Country As Range
    Dim DisCount As Range, Quantity As Range, ItemName As Range
    Dim OrderName As Range, RequiredData As Range, wsOrder As Worksheet
    Dim wsResult As Worksheet, wsCondition As Worksheet, wbOrder As Workbook
    Dim wbCondition As Workbook, OrderFile As String, ConditionFile As String

    OrderFile = Application.GetOpenFilename() 'Open Order wb
    Set wbOrder = Workbooks.Open(OrderFile)
    Set wsOrder = wbOrder.Worksheets(1)
    ConditionFile = Application.GetOpenFilename() 'Open Condition wb
    Set wbCondition = Workbooks.Open(ConditionFile)
    Set wsResult = wbCondition.Worksheets(1)
    Set wsCondition = wbCondition.Worksheets(2)
    myCount = Application.CountA(wsCondition.Range("A:A")) 'use CountA (all non-blanks)
    For i = 2 To myCount Step 1
        strKeyWord = wsCondition.Range("A" & i)
        wsOrder.Range("R:R").AutoFilter Field:=1, Criteria1:="=*" & strKeyWord & "*"
        If wsOrder.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            Set SubTotal = wsOrder.Range("I2", wsOrder.Range("I" & Rows.Count).End(xlUp))
            Set Country = wsOrder.Range("AG2", wsOrder.Range("AG" & Rows.Count).End(xlUp))
            Set DisCount = wsOrder.Range("N2", wsOrder.Range("N" & Rows.Count).End(xlUp))
            Set Quantity = wsOrder.Range("Q2", wsOrder.Range("Q" & Rows.Count).End(xlUp))
            Set OrderName = wsOrder.Range("A2", wsOrder.Range("A" & Rows.Count).End(xlUp))
            Set ItemName = wsOrder.Range("R2", wsOrder.Range("R" & Rows.Count).End(xlUp))
            Set RequiredData = Union(SubTotal, Country, _
                        DisCount, Quantity, OrderName, ItemName)
            RequiredData.SpecialCells(xlCellTypeVisible).Copy
            OrderCount = wsOrder.Range("A2", wsOrder.Range("A" & Rows.Count) _
                        .End(xlUp)).SpecialCells(xlCellTypeVisible).Cells.Count
            With wsResult
                If OrderCount >= 2 Then
                    For N = 1 To OrderCount Step 1
                        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = strKeyWord
                        .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = "Available"
                    Next N
                Else
                    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = strKeyWord
                    .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = "Available"
                End If
                .Cells(.Rows.Count, "C").End(xlUp).Offset(1).PasteSpecial
            End With
        Else
            With wsResult
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = strKeyWord
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = "No Order"
                .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Value = "N/A"
                .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Value = "N/A"
                .Cells(.Rows.Count, "E").End(xlUp).Offset(1).Value = "N/A"
            End With
        End If
        OrderCount = 0
    Next i
    wbCondition.Sheets("Result").Activate
    wsOrder.AutoFilterMode = False
End Sub

Upvotes: 0

Views: 185

Answers (1)

DisplayName
DisplayName

Reputation: 13386

it must be because your "condition" file is a post 2003 excel workbook while your not working "order" file is an up to 2003 excel workbook.

hence your "condition" workbook worksheets have some 1 million rows each, while your "order" workbook worksheets have some 65k rows each

in:

OrderCount = wsOrder.Range("A2", wsOrder.Range("A" & Rows.Count) _
                    .End(xlUp)).SpecialCells(xlCellTypeVisible).Cells.Count

that unqualified Rows.Count is implicitly assuming the active sheet as the one to count the rows of

the last opened workbook is "condition" one, so wsOrder.Range("A" & Rows.Count) is trying to access "order" workbook sheet some 1million th row, while it has up to 65 k...

a solution could be simply swap the workbook opening sequence:

ConditionFile = Application.GetOpenFilename() 'Open Condition wb first
Set wbCondition = Workbooks.Open(ConditionFile)
Set wsResult = wbCondition.Worksheets(1)
Set wsCondition = wbCondition.Worksheets(2)

OrderFile = Application.GetOpenFilename() 'Open Order wb as the last one
Set wbOrder = Workbooks.Open(OrderFile)
Set wsOrder = wbOrder.Worksheets(1)

thus having "order" workbook as the active one

but is best practice to avoid relying on Active/Selection coding pattern and use fully qualified range references instead:

OrderCount = wsOrder.Range("A2", wsOrder.Range("A" & wsOrder.Rows.Count) _
                    .End(xlUp)).SpecialCells(xlCellTypeVisible).Cells.Count

so I'd suggest you this latter fix

Upvotes: 1

Related Questions