Greetings all!
I needed a super search VBA for my sheet and my friend and I Frankenstein-ed one from various other VBA we found. The VBA seems to work fairly well with one issue that if the search box is blank/empty then it returns with the following error Run-time error'91' object variable or with block variable not set and it seems to be caused by the line Hiders.EntireRow.Hidden = True within the code. If anyone with more superior VBA knowledge than mine can assist it would be greatly appreciated. There are 3 codes that basically allows this to work
On the workbook module:
Code
Sub Show_Only_Dept()
Dim r As Integer
Dim Hiders As Range, Found As Range
Dim Cond As String
Rows("4:5000").Hidden = False
'
'The department you want displayed is from cell B1
Cond = [allocation!B1].Value
'
Application.ScreenUpdating = False
Call Show_All 'Unhide previous condition if any
For r = 4 To 5000 'Rows to check
Set Found = Range("A" & r, "BD" & r).Find(What:=Cond, _
After:=Range("A" & r), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Found Is Nothing Then
If Hiders Is Nothing Then
Set Hiders = Rows(r)
Else
Set Hiders = Union(Hiders, Rows(r))
End If: End If: Next r
Hiders.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub
Display More
Follow by
Then within the worksheet
Code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
' call your sub
Call Show_Only_Dept
End If
End Sub
Many many thanks in advance for whoever can catch this bug as I have pulled out most of my hair trying to work it out!!