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)

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)

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?

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)

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

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

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!

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

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!