Object variable or With Block variable not set

[sparky2205]

Hi folks,
in the following code I'm getting the above error.

Sub Filter_By_Category()

Dim lastrow As Long
Dim count As Long
Dim count1 As Long
Dim lvalue As Long
Dim category As String
Dim srange As Long

    srange = 9
    lastrow = Cells(Rows.count, 3).End(xlUp).Row
    ' Hide all vendors
    Rows("9:" & lastrow).EntireRow.Hidden = True
    category = Application.InputBox("By which Category do you wish to filter?", Type:=2)
    count = WorksheetFunction.CountIf(Range("I9:I" & lastrow), category)

    For i = 1 To count
        If i = 1 Then
            lvalue = Range("I" & srange & ":I" & lastrow).Find(category).Row + 8
        Else
            lvalue = Range("I" & srange & ":I" & lastrow).Find(category).Row
        End If
'        lvalue = Application.Match(category, Sheets("Revised").Range("I"& srange & ":I" & lastrow), 0) + 8
        Rows(lvalue & ":" & (lvalue + 3)).Hidden = False
        srange = lvalue + 3
    Next i

    'lvalue = WorksheetFunction.XLookup(category, Range("I9:I" & lastrow), Range("I9:I" & lastrow), "Selected category not found")

End Sub

It errors at:

lvalue = Range("I" & srange & ":I" & lastrow).Find(category).Row + 8

i.e. the first IF statement.
"category" is populated with a value that exists in column I

It worked fine yesterday, but has decided not to work today.
Any thoughts?

 

[RoryA]

Find remembers all the options set in the last search, whether in code or in the UI, so you should always specify all of them. You should always also check if a range was returned by Find before you try to use it.

 

[sparky2205]

Thanks for the response RoryA.
Could you be more specific on how I can fix this?
I don't know what you mean by, "you should always specify all of them".
And I don't know how to check if a range was returned by Find, as the error is generated at the line where the Find is.

 

[RoryA]

What I mean is that you'd use a Range variable to hold the result, then check if that is Nothing before proceeding:

Dim FoundRange as Range
set FoundRange = Range("I" & srange & ":I" & lastrow).Find(what:=category, lookin:=xlvalues, lookat:=xlwhole, MatchCase:=False)

those arguments should fix the most common find parameters, but you may also want to add SearchFormat:=False

 

[sparky2205]

I've tried it with those arguments, including SearchFormat:=False, but I still get the same error.

"What I mean is that you'd use a Range variable to hold the result, then check if that is Nothing before proceeding:"
It is when I try to assign the result to a variable that I get the error. So I don't have the opportunity to check.
Or am I misunderstanding you again?

 

[Alex Blakenburg]

Do you have any formulas in Column I ? If you do I will need to modify the below.
Since you are using Find with rows being hidden LookIn needs to be xlFormulas.

This incorporates Rory's suggestion with the LookIn changed per the above:

Sub Filter_By_Category()

Dim lastrow As Long
Dim count As Long
Dim count1 As Long
Dim lvalue As Long
Dim category As String
Dim srange As Long
Dim i As Long

    srange = 9
    lastrow = Cells(Rows.count, 3).End(xlUp).Row
    ' Hide all vendors
    Rows("9:" & lastrow).EntireRow.Hidden = True
    category = Application.InputBox("By which Category do you wish to filter?", Type:=2)
    count = WorksheetFunction.CountIf(Range("I9:I" & lastrow), category)

    Dim FoundRange As Range
    For i = 1 To count
        ' LookIn xlFormulas - the values option won't look in Hidden rows
        Set FoundRange = Range("I" & srange & ":I" & lastrow).Find(what:=category, _
                            LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)
        If FoundRange Is Nothing Then Exit For
            If i = 1 Then
                lvalue = FoundRange.Row + 8
            Else
                lvalue = FoundRange.Row
            End If
    '        lvalue = Application.Match(category, Sheets("Revised").Range("I"& srange & ":I" & lastrow), 0) + 8
            Rows(lvalue & ":" & (lvalue + 3)).Hidden = False
            srange = lvalue + 3
    Next i

    'lvalue = WorksheetFunction.XLookup(category, Range("I9:I" & lastrow), Range("I9:I" & lastrow), "Selected category not found")

End Sub

 

[sparky2205]

Hi Alex,
I had done a bit more research on this and found the reference to LookIn needing to be Lookin:=xlFormulas.
I tried that with my existing code but I still got the same error.

I have now used your code and FoundRange does indeed appear to be Nothing.
But column I is populated by formulas. You mentioned above that you would need to adjust your code to reflect this.

 

[Alex Blakenburg]

Try this, changes are in blue:

Sub Filter_By_Category()

Dim lastrow As Long
Dim count As Long
Dim count1 As Long
Dim lvalue As Long
Dim category As String
Dim srange As Long
Dim i As Long

    srange = 9
    lastrow = Cells(Rows.count, 3).End(xlUp).Row
    ' Hide all vendors
    Rows("9:" & lastrow).EntireRow.Hidden = True
    category = Application.InputBox("By which Category do you wish to filter?", Type:=2)
    count = WorksheetFunction.CountIf(Range("I9:I" & lastrow), category)
    
    Dim FoundRange As Range
    For i = 1 To count
        ' LookIn xlFormulas - the values option won't look in Hidden rows
        Set FoundRange = Range("I" & srange & ":I" & lastrow).Find(what:=category, _
                            LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=False)
        If FoundRange Is Nothing Then Exit For
        If FoundRange.Value = category Then
            If i = 1 Then
                lvalue = FoundRange.Row + 8
            Else
                lvalue = FoundRange.Row
            End If
    '        lvalue = Application.Match(category, Sheets("Revised").Range("I"& srange & ":I" & lastrow), 0) + 8
            Rows(lvalue & ":" & (lvalue + 3)).Hidden = False
            srange = lvalue + 3
        End If
    Next i

    'lvalue = WorksheetFunction.XLookup(category, Range("I9:I" & lastrow), Range("I9:I" & lastrow), "Selected category not found")

End Sub

 

[sparky2205]

I don't get the error now but it doesn't filter anything either.
I have 5 options for category: A, B, C, D, Customer Supplied.
On the spreadsheet each category has 4 lines the category being on the first line in column I only.
What I'm trying to do is allow the user to click a button, enter the category and have the spreadsheet display all results for that category. i.e. all 4 lines for each selected category for each vendor. e.g. there should be 34 entries for category A.
When I run through your code, regardless of what category I select, FoundRange always appears = Customer Supplied.
I9 is the first cell where category is listed and this value is Customer Supplied. This may be coincidence but I though I'd mention it anyway.

 

[Alex Blakenburg]

Without visibility of your data I don't full understand what you are trying to do but try and replace the loop with this and see if that works:

    Dim FoundRow As Long
    For i = 1 To count
        With Application
            FoundRow = .IfError(.Match(category, Range("I" & srange & ":I" & lastrow), 0), 0)
        End With
        If FoundRow = 0 Then Exit For
        If i = 1 Then
            lvalue = FoundRow + 8
        Else
            lvalue = FoundRow
        End If
'        lvalue = Application.Match(category, Sheets("Revised").Range("I"& srange & ":I" & lastrow), 0) + 8
        Rows(lvalue & ":" & (lvalue + 3)).Hidden = False
        srange = lvalue + 3
    Next i