Subscript Out of Range

[joshcas]

Hi
I’m having a lot of trouble with a sentence of my code because it’s returning a subscript out of range error, what my code is trying to do is filtering the data, base on the user input and if the word exists then show that in the lisbox, if not, show a message to the user, I’m sure there is an easiest way, but I’m good if I can get rid of the error <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Private Sub Image2_Click()
'///////////////////////////////////////////////////////////////////////////////////////////////////////
' Search Button ----------------------------------------------------------------------------------
'///////////////////////////////////////////////////////////////////////////////////////////////////////
 
' Show searching label ...
Image12.Visible = True
 
  With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .EnableEvents = False
    End With
 
'Application.Calculation = xlCalculationAutomatic
'On Error GoTo ErrorHandler
 
'EnableEvents = False
'Application.ScreenUpdating = False
 
ListBox2.RowSource = ""
ListBox1.RowSource = ""
 
 
Dim T1
Dim T2
Dim T3
Dim T4
T1 = TextBox1
T2 = TextBox2
T3 = ComboBox1
T4 = ComboBox2
 
'Sheets("Temp3").Visible = True
'Sheets("Temp3").Select
'Cells.Select
'Selection.delete Shift:=xlUp
'Cells.Select
'Selection.delete Shift:=xlUp
'Cells.Select
'Selection.delete Shift:=xlUp
 
Sheets("Temp3").Cells.ClearContents
'Sheets("Temp").Visible = True
'Sheets("Temp").Select
'
'Range("A1").Select
'    Selection.AutoFilter
'    Selection.AutoFilter
 
Sheets("Temp").AutoFilterMode = False
Sheets("Temp").Range("A1").AutoFilter
Sheets("Temp").Range("A1").AutoFilter
  '  Sheets("temp").Range("A1").AutoFilter
 
 
 
   Dim LR As Long:
 
 
   LR = Sheets("temp").Range("A" & Rows.Count).End(xlUp).Row ' this code is to find the lastest cell with info
If T1 <> "" Then
'    ActiveSheet.Range("$A$1:L" & LR).AutoFilter Field:=1, Criteria1:="*" & T1 & "*"
'       Range("A1").Select
 
       Sheets("temp").Range("$A$1:L" & LR).AutoFilter Field:=1, Criteria1:="*" & T1 & "*"
 
Dim rng As Range
'Sheets("Temp").Range("A1").Select
'Dim rFoundCell As Range
'
' Set rFoundCell1 = Range("A1")
 ' With Sheets(temp)
'  Set rng = Cells.Find(What:=T1, After:=rFoundCell, LookIn:=xlFormulas, LookAt _
'        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
'        False, SearchFormat:=False)
 
' Error here !!!!! //////////////////////////
 
           Set rng = Sheets(temp).Cells.Find(What:=T1, After:=Range("A1"), _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)
'End With
If rng Is Nothing Then
 ' ActiveSheet.Range("$A$1:$K$46938").AutoFilter Field:=1
   Sheets("Temp").Range("$A$1:$K$999999").AutoFilter Field:=1
MsgBox "Not Found"
ListBox1.RowSource = ""
GoTo nope
 
End If
 
End If

 

[Andrew Poulsom]

Welcome to MrExcel.

You need:

Set rng = Sheets("temp").Cells.Find(What:=T1, After:=Range("A1"), _

 

[ChrisM]

and also:

With Sheets("temp")

 

[joshcas]

Great solution , thank you so much , although I didn’t re-enable the “With Sheets(“Temp”) “ because I got the same error on that sentence .<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>