Search Multiple Sheets Then Add Txt1.Txt to Cell
 

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

Search Multiple Sheets Then Add Txt1.Txt to Cell
 

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.



" wrote:

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

Search Multiple Sheets Then Add Txt1.Txt to Cell
 

On Oct 31, 8:27 am, JRForm wrote:
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.



" wrote:
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- Hide quoted text -

- 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