PDA

View Full Version : Solved: Search form



matty25
11-03-2008, 12:56 PM
Hi All,

I have the following code on frmSearch (userform) which i need to add some code to so that it presents a msgbox error if no results found when i click the findall button e.g. msgbox "No search results found", the search is defined by the user inputted text/string inputted in TextBox1 and the results are shown/filtered in ListBox1.



Private Sub UserForm_Initialize()
TextBox1.SetFocus
ListBox1.ColumnCount = 2
Set myData = Sheet1.Range("a2").CurrentRegion
With Me
.Caption = "Search Database"
End With
End Sub

Sub cmbFindAll_Click()
Dim strFind As String
Dim rFilter As Range
Dim ii As Long
Dim c As Range, a() As String, n As Long
Set rFilter = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:="*" & strFind & "*"
Set rng = Sheet1.Range("a2", Range("a65536").End(xlUp))
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
For Each c In rng
n = n + 1: ReDim Preserve a(0 To 11, 1 To n)
For ii = 0 To 11
a(ii, n) = c.Offset(, ii).Value
Next
Next
End With
If n > 0 Then Me.ListBox1.Column = a
End Sub



Any help you can offer would be much appreciated.

Many thanks, Matt

fb7894
11-03-2008, 02:51 PM
Sub FindFunction()
Dim rFound As Range
On Error Resume Next

With Sheet1
Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=False)

On Error GoTo 0

If Not rFound Is Nothing Then
MsgBox "No Records Found"
End With

End Sub

fb7894
11-03-2008, 02:51 PM
Sub FindFunction()
Dim rFound As Range
On Error Resume Next

With Sheet1
Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=False)

On Error GoTo 0

If Not rFound Is Nothing Then
MsgBox "No Records Found"
End With

End Sub

fb7894
11-03-2008, 02:52 PM
sorry, posted twice by mistake.

matty25
11-04-2008, 10:59 AM
Sub FindFunction()
Dim rFound As Range
On Error Resume Next

With Sheet1
Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=False)

On Error GoTo 0

If Not rFound Is Nothing Then
MsgBox "No Records Found"
End With

End Sub


Thanks fb, but the highlighted text above is coming up as a syntax error in red, any ideas? Thanks for your help so far.

mdmackillop
11-04-2008, 11:07 AM
Sub FindFunction()
Dim rFound As Range
On Error Resume Next
With Sheet1
Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
On Error GoTo 0
If rFound Is Nothing Then
MsgBox "No Records Found"
End If
End With
End Sub

matty25
11-04-2008, 11:49 AM
A real big thank you for your replies.

Here is my full code for the search form. Not sure why but when the text user entered in TextBox1 doesnt match/find any results the error msgbox isnt popping up saying no results found, instead it just shows "Trade" and "Company Name" on one line in the listbox as a search result.



Option Explicit
Dim MyArray(6, 4)
Public myData As Range, c As Range
Dim rFound As Range
Dim r As Long
Dim rng As Range
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
With ThisWorkbook.Worksheets("Sheet1")
If .FilterMode Then
.ShowAllData
TextBox1.Text = ""
TextBox1.SetFocus
ListBox1.Clear
End If
End With
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Load frmRecord
frmRecord.TextBox1.Value = Me.ListBox1.Column(1)
frmRecord.TextBox2.Value = Me.ListBox1.Column(2)
frmRecord.TextBox3.Value = Me.ListBox1.Column(3)
frmRecord.TextBox4.Value = Me.ListBox1.Column(4)
frmRecord.TextBox5.Value = Me.ListBox1.Column(5)
frmRecord.TextBox6.Value = Me.ListBox1.Column(6)
frmRecord.TextBox7.Value = Me.ListBox1.Column(7)
frmRecord.TextBox8.Value = Me.ListBox1.Column(8)
frmRecord.TextBox9.Value = Me.ListBox1.Column(9)
frmRecord.TextBox10.Value = Me.ListBox1.Column(10)
frmRecord.TextBox11.Value = Me.ListBox1.Column(11)
frmRecord.Show
End Sub
Private Sub UserForm_Initialize()
TextBox1.SetFocus
ListBox1.ColumnCount = 2
Set myData = Sheet1.Range("a2").CurrentRegion 'database
With Me
.Caption = "Search Database" 'userform caption
End With
End Sub
Sub cmbFindAll_Click()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Dim ii As Long
Dim c As Range, a() As String, n As Long
Set rFilter = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:="*" & strFind & "*"
Set rng = Sheet1.Range("a2", Range("a65536").End(xlUp))
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
For Each c In rng
n = n + 1: ReDim Preserve a(0 To 11, 1 To n)
For ii = 0 To 11
a(ii, n) = c.Offset(, ii).Value
Next
Next
End With
If n > 0 Then Me.ListBox1.Column = a
End Sub
Sub FindFunction()
Dim rFound As Range
On Error Resume Next
With Sheet1
Set rFound = .Columns(1).Find(What:="Cat", After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
On Error GoTo 0
If rFound Is Nothing Then
MsgBox "No Records Found"
End If
End With
End Sub


Any help you can offer would be much appreciated.

Cheers, Matt

mdmackillop
11-04-2008, 12:37 PM
Hi Matt,
Can you post a workbook containing your form?

matty25
11-04-2008, 12:48 PM
Hi

Here's my workbook:

http://rapidshare.com/files/160666811/Tender_List.xls.html

Cheers, Matt

mdmackillop
11-04-2008, 03:48 PM
Apologies Matt,
I responded correcting fb's code, not your original query
try
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
If rng.Row = 1 Then
MsgBox "Nothing found"
End If

matty25
11-05-2008, 11:32 AM
Excellent, thank you so much, i amended the code slightly to this which works a treat:



Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
If rng.Row = 1 Then
ListBox1.Clear
MsgBox "No records found"


Many thanks, Matt

lucas
11-05-2008, 11:37 AM
Matt, please mark your thread solved using the thread tools at the top of the page.