Search Multiple Sheets Then Add Txt1.Txt to Cell

[gwoodby]

Here is my issue i have been Trying to figure this out for about a
day and a half

Im Trying to Search 3 Sheets
Sheet 1 Is Shelter
Sheet 2 is NonShelter
Sheet 3 is TPR
I need it to Search 3 Sheets column A For the name, Now the Names
will be like this in column A

Hubbard, John
Hubbard, Rick

Etc Etc so It Has to pull up multiple Names Placing them in a
listbox on a different form
any ideas??? Im Really Confused \

I'm Still very new to vba so Im sure my coding sucks


Private Sub CmdSearch_Click()
Dim sh As Worksheet
Dim rng As Range, firstAddress As String
Dim SearchTxt As String
SearchTxt = TxtCaseName.Text

For Each sh In ThisWorkbook.Worksheets
Set rng = sh.Cells.Find(What:=SearchTxt, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
Set rng = sh.Cells.FindNext(rng)
FrmSelection.LboxSelect.AddItem (rng)
Loop Until rng.Address = firstAddress
End If
Next
If FrmSelection.LboxSelect.Value < 1 Then
Unload Me
FCreate.Show
Else
Unload Me
FrmSelection.Show
End If

End Sub

 

[Guest]

Try changing the code line

From> FrmSelection.LboxSelect.Value < 1 Then

TO> FrmSelection.LboxSelect.ListCount < 1 Then


Each time you additem to a list box the count goes up. So by testing for
values in the list (ListCount) is easy way to find out if you need to close
the form and go on the FCreate form.

 

[gwoodby]

Try changing the code line

From> FrmSelection.LboxSelect.Value < 1 Then

TO> FrmSelection.LboxSelect.ListCount < 1 Then

Each time you additem to a list box the count goes up. So by testing for
values in the list (ListCount) is easy way to find out if you need to close
the form and go on the FCreate form.












- Show quoted text -

For Some Reason Its not Searching :| Fsearch Is the First Userform,
FrmSelection is the Second userform
They Put the name in on Fsearch Click the "Search" Button, Then
shoudl Return the results on FrmSelection inside a listbox I would
Like it if It Had the name, and the sheet name in the listbox any
ideas on why its not searching and how to add this feature? Im
stumped ill be working on it all day... any help would be greatly
appreciated

 

[Guest]

Okay. Let's break it down this way. I have created a userform and named it
FrmSelection. I have added a list box and named it LboxSelect and like wise I
added a text box and named it TxtCaseName. Lastly I added a command button
and named it CmdSearch. I placed the code below behind the CmdSearch button
in the Click event. I remmed out the code lines:

'If FrmSelection.LboxSelect.ListCount < 1 Then
' Unload Me
' FCreate.Show
' Else
' Unload Me
' FrmSelection.Show
' End If

'**Paste in Code below
*********************************************************
'***********************************************************

Dim sh As Worksheet
Dim rng As Range, firstAddress As String
Dim SearchTxt As String
SearchTxt = TxtCaseName.Text

For Each sh In ThisWorkbook.Worksheets

Set rng = sh.Cells.Find(What:=SearchTxt, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rng Is Nothing Then
firstAddress = rng.Address

Do
Set rng = sh.Cells.FindNext(rng)
FrmSelection.LboxSelect.AddItem (rng) & " " & sh.Name
Loop Until rng.Address = firstAddress

End If

Next sh

End Sub

******************************************************************EOC***************************