AutoFilter in Excel 2007

[sprimax]

Hi,

I have an AutoFilter in column B (range B12:B342), but I am wondering how to make a msgbox "record not found" if the string is not found after AutoFilter. The code in second paragraph below is not working.

I really appreciate it if you could lead me ... thank you

Private Sub CommandButton1_Click()
' FIND button
Dim kriteria1 As String
'Dim kriteria2 As String
'Dim kriteria3 As String
kriteria1 = "*" & Me.TextBox1.Value & "*"
Me.TextBox1.SetFocus
'check for a value & show message
If TextBox1.Value = "" Then 'the textbox in userform
Me.TextBox1.SetFocus
MsgBox "Please enter a value to find.", , "Item Number"

ElseIf Range("B12:B342").SpecialCells(xlVisible).Rows.Count = 0 Then
MsgBox "Item Number Not Found", , "Not Found"
Beep

Else: CommandButton1.SetFocus
Sheets("ANALYZER").Select
Range("A11:CA11").Select
Selection.AutoFilter
Sheets("ANALYZER").Select
Range("A11").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A11:CA11").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=kriteria1
Beep
End If
End Sub

 

[venkat1926]

not clear

"ElseIf Range("B12:B342").SpecialCells(xlVisible).Rows.Count = 0 Then"

you use "visible cells". that means that some rows are hidden either due to autofilter or due to deliberate hiding. I do not find any of these in the code.

some ambiguity

quote
Range("A11:CA11").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=kriteria1
unquote
you select only one row and autofilter. something is wrong.
also too may "select". for many procedures explicit sslection may not be necessary.

you rewrite the code. introduce some msgboix(s) to debug and check whether macro does what you want which can be removed later.