Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching only Column A
Im trying to Search Only Column a on Multiple Sheets, Return the values that mach, however When using my Code (Pasted Below) It Still Searches All Columns... Why is this? Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching only Column A
You would probably have to use a syntax like:
Set StrRng = sh.Range("A1:A65536").Find(What:=StrSearching, for it to recognize the restricted range limits in the Object Variable. Columns won't do it. " wrote: Im trying to Search Only Column a on Multiple Sheets, Return the values that mach, however When using my Code (Pasted Below) It Still Searches All Columns... Why is this? Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching only Column A
On Dec 3, 1:39 pm, JLGWhiz wrote:
You would probably have to use a syntax like: Set StrRng = sh.Range("A1:A65536").Find(What:=StrSearching, for it to recognize the restricted range limits in the Object Variable. Columns won't do it. " wrote: Im trying to Search Only Column a on Multiple Sheets, Return the values that mach, however When using my Code (Pasted Below) It Still Searches All Columns... Why is this? Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False)- Hide quoted text - - Show quoted text - I only want it to Search Column A, Is there any way to search only Column a ? or better yet only search the first cell of each row? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching only Column A
Your posted code should only be searching column A. What makes you think it
is doing something else... Perhaps post some more code. -- HTH... Jim Thomlinson " wrote: Im trying to Search Only Column a on Multiple Sheets, Return the values that mach, however When using my Code (Pasted Below) It Still Searches All Columns... Why is this? Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching only Column A
On Dec 3, 2:36 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: Your posted code should only be searching column A. What makes you think it is doing something else... Perhaps post some more code. -- HTH... Jim Thomlinson " wrote: Im trying to Search Only Column a on Multiple Sheets, Return the values that mach, however When using my Code (Pasted Below) It Still Searches All Columns... Why is this? Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False)- Hide quoted text - - Show quoted text - Here is the Code im using, I have 3 sheets,If i were to put the name fry, Mcdonald in column A, and fry, BurgerKing in column B when it pulls up the listbox both show, though only 1 should be showing.. Private Sub CmdSearch_Click() Dim sh As Worksheet Dim StrRng As Range Dim StrSearching As String Dim Found As Integer Found = 0 StrSearching = TxtCaseName.Text If TxtCaseName.Text = "" Then GoTo ErrorHandler End If For Each sh In ThisWorkbook.Worksheets sh.Activate Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) IntNumber = MySearch(StrRng) If IntNumber 0 Then Found = Found + 1 End If Next sh If Found 0 Then Unload Me FrmSelection.Show Else Unload Me FCreate.Show End If ErrorHandler: MsgBox "Please Type a Name", vbInformation, " No Names found" End Sub Function MySearch(StrRng As Range) Dim firstAddress As String Dim IntNumber As Integer IntNumber = 0 If Not StrRng Is Nothing Then firstAddress = StrRng.Address MySearch = IntNumber + 1 Do Set StrRng = Cells.FindNext(StrRng) FrmSelection.LboxSelect.AddItem (StrRng) Loop While Not StrRng Is Nothing And StrRng.Address < firstAddress Else MySearch = IntNumber End If End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching only Column A
In this line you are checking the entire worksheet an dnot just column A...
Set StrRng = Cells.FindNext(StrRng) Cells will be all cells in the active sheet. -- HTH... Jim Thomlinson " wrote: On Dec 3, 2:36 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Your posted code should only be searching column A. What makes you think it is doing something else... Perhaps post some more code. -- HTH... Jim Thomlinson " wrote: Im trying to Search Only Column a on Multiple Sheets, Return the values that mach, however When using my Code (Pasted Below) It Still Searches All Columns... Why is this? Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False)- Hide quoted text - - Show quoted text - Here is the Code im using, I have 3 sheets,If i were to put the name fry, Mcdonald in column A, and fry, BurgerKing in column B when it pulls up the listbox both show, though only 1 should be showing.. Private Sub CmdSearch_Click() Dim sh As Worksheet Dim StrRng As Range Dim StrSearching As String Dim Found As Integer Found = 0 StrSearching = TxtCaseName.Text If TxtCaseName.Text = "" Then GoTo ErrorHandler End If For Each sh In ThisWorkbook.Worksheets sh.Activate Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) IntNumber = MySearch(StrRng) If IntNumber 0 Then Found = Found + 1 End If Next sh If Found 0 Then Unload Me FrmSelection.Show Else Unload Me FCreate.Show End If ErrorHandler: MsgBox "Please Type a Name", vbInformation, " No Names found" End Sub Function MySearch(StrRng As Range) Dim firstAddress As String Dim IntNumber As Integer IntNumber = 0 If Not StrRng Is Nothing Then firstAddress = StrRng.Address MySearch = IntNumber + 1 Do Set StrRng = Cells.FindNext(StrRng) FrmSelection.LboxSelect.AddItem (StrRng) Loop While Not StrRng Is Nothing And StrRng.Address < firstAddress Else MySearch = IntNumber End If End Function |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching only Column A
On Dec 3, 3:32 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: In this line you are checking the entire worksheet an dnot just column A... Set StrRng = Cells.FindNext(StrRng) Cells will be all cells in the active sheet. -- HTH... Jim Thomlinson " wrote: On Dec 3, 2:36 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Your posted code should only be searching column A. What makes you think it is doing something else... Perhaps post some more code. -- HTH... Jim Thomlinson " wrote: Im trying to Search Only Column a on Multiple Sheets, Return the values that mach, however When using my Code (Pasted Below) It Still Searches All Columns... Why is this? Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False)- Hide quoted text - - Show quoted text - Here is the Code im using, I have 3 sheets,If i were to put the name fry, Mcdonald in column A, and fry, BurgerKing in column B when it pulls up the listbox both show, though only 1 should be showing.. Private Sub CmdSearch_Click() Dim sh As Worksheet Dim StrRng As Range Dim StrSearching As String Dim Found As Integer Found = 0 StrSearching = TxtCaseName.Text If TxtCaseName.Text = "" Then GoTo ErrorHandler End If For Each sh In ThisWorkbook.Worksheets sh.Activate Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) IntNumber = MySearch(StrRng) If IntNumber 0 Then Found = Found + 1 End If Next sh If Found 0 Then Unload Me FrmSelection.Show Else Unload Me FCreate.Show End If ErrorHandler: MsgBox "Please Type a Name", vbInformation, " No Names found" End Sub Function MySearch(StrRng As Range) Dim firstAddress As String Dim IntNumber As Integer IntNumber = 0 If Not StrRng Is Nothing Then firstAddress = StrRng.Address MySearch = IntNumber + 1 Do Set StrRng = Cells.FindNext(StrRng) FrmSelection.LboxSelect.AddItem (StrRng) Loop While Not StrRng Is Nothing And StrRng.Address < firstAddress Else MySearch = IntNumber End If End Function- Hide quoted text - - Show quoted text - TYVM I never even thought of that! Appreciate it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching only Column A
It is your "MySearch" function that is searching all cells and loading your
listbox. It uses the object Cells as it's search range and that is the whole sheet. The function also loads your listbox with the AddItem method. Jim was right about the other find code. I could not get it to return anything that was not in column A. " wrote: On Dec 3, 2:36 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Your posted code should only be searching column A. What makes you think it is doing something else... Perhaps post some more code. -- HTH... Jim Thomlinson " wrote: Im trying to Search Only Column a on Multiple Sheets, Return the values that mach, however When using my Code (Pasted Below) It Still Searches All Columns... Why is this? Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False)- Hide quoted text - - Show quoted text - Here is the Code im using, I have 3 sheets,If i were to put the name fry, Mcdonald in column A, and fry, BurgerKing in column B when it pulls up the listbox both show, though only 1 should be showing.. Private Sub CmdSearch_Click() Dim sh As Worksheet Dim StrRng As Range Dim StrSearching As String Dim Found As Integer Found = 0 StrSearching = TxtCaseName.Text If TxtCaseName.Text = "" Then GoTo ErrorHandler End If For Each sh In ThisWorkbook.Worksheets sh.Activate Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) IntNumber = MySearch(StrRng) If IntNumber 0 Then Found = Found + 1 End If Next sh If Found 0 Then Unload Me FrmSelection.Show Else Unload Me FCreate.Show End If ErrorHandler: MsgBox "Please Type a Name", vbInformation, " No Names found" End Sub Function MySearch(StrRng As Range) Dim firstAddress As String Dim IntNumber As Integer IntNumber = 0 If Not StrRng Is Nothing Then firstAddress = StrRng.Address MySearch = IntNumber + 1 Do Set StrRng = Cells.FindNext(StrRng) FrmSelection.LboxSelect.AddItem (StrRng) Loop While Not StrRng Is Nothing And StrRng.Address < firstAddress Else MySearch = IntNumber End If End Function |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching only Column A
On Dec 3, 3:37 pm, JLGWhiz wrote:
It is your "MySearch" function that is searching all cells and loading your listbox. It uses the object Cells as it's search range and that is the whole sheet. The function also loads your listbox with the AddItem method. Jim was right about the other find code. I could not get it to return anything that was not in column A. " wrote: On Dec 3, 2:36 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Your posted code should only be searching column A. What makes you think it is doing something else... Perhaps post some more code. -- HTH... Jim Thomlinson " wrote: Im trying to Search Only Column a on Multiple Sheets, Return the values that mach, however When using my Code (Pasted Below) It Still Searches All Columns... Why is this? Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False)- Hide quoted text - - Show quoted text - Here is the Code im using, I have 3 sheets,If i were to put the name fry, Mcdonald in column A, and fry, BurgerKing in column B when it pulls up the listbox both show, though only 1 should be showing.. Private Sub CmdSearch_Click() Dim sh As Worksheet Dim StrRng As Range Dim StrSearching As String Dim Found As Integer Found = 0 StrSearching = TxtCaseName.Text If TxtCaseName.Text = "" Then GoTo ErrorHandler End If For Each sh In ThisWorkbook.Worksheets sh.Activate Set StrRng = sh.Columns(1).Find(What:=StrSearching, After:=Columns.Range("A5"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) IntNumber = MySearch(StrRng) If IntNumber 0 Then Found = Found + 1 End If Next sh If Found 0 Then Unload Me FrmSelection.Show Else Unload Me FCreate.Show End If ErrorHandler: MsgBox "Please Type a Name", vbInformation, " No Names found" End Sub Function MySearch(StrRng As Range) Dim firstAddress As String Dim IntNumber As Integer IntNumber = 0 If Not StrRng Is Nothing Then firstAddress = StrRng.Address MySearch = IntNumber + 1 Do Set StrRng = Cells.FindNext(StrRng) FrmSelection.LboxSelect.AddItem (StrRng) Loop While Not StrRng Is Nothing And StrRng.Address < firstAddress Else MySearch = IntNumber End If End Function- Hide quoted text - - Show quoted text - I had Forgot about the code to findnext and didnt specify it to only search column a but its working now all the help is greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching One Column Only | Excel Discussion (Misc queries) | |||
Column Searching | Excel Programming | |||
Searching a column | Excel Programming | |||
Searching a Column | Excel Programming | |||
Searching In a Column | Excel Programming |