Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search, copy and paste through multiple sheets | Excel Discussion (Misc queries) | |||
Create a cell that will search multiple sheets for entered string. | Excel Discussion (Misc queries) | |||
Search in Multiple Sheets | Excel Discussion (Misc queries) | |||
search multiple sheets for specific date, return data in cell to r | Excel Discussion (Misc queries) | |||
Search across multiple sheets and subtotal. | Excel Programming |