ID Number Col A

[Hazel]

Hi All

Elsewhere in this forum I found the following which I have altered to suit
my project.

Private Sub Find_Click()
Dim wksToSearch As Worksheets
Dim rngToSearch As Range
Dim rngFound As Range

Set wksToSearch = Sheets("ALTON")
Set rngToSearch = wksToSearch.Columns("A")
Set rngFound = rngToSearch.Find(What:=TextBox21.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & TextBox21.Value & " was not found."
Else
wksToSearch.Select
rngFound.Select
End If

End Sub

and it works ok on the named sheet however there are 26 named sheets in the
workbook is it possible to search all the sheets after entering the ID number
in TextBox21. When it finds the ID Number on whatever row in whatever sheet
In TB2 on the Userform I would want it to show the value of ColumnB -- TB3
would show the value of Column C -- Tb4 would show the value of Column D.

 

[Joel]

Private Sub Find_Click()
'COMMENTED THIS LINE OF CODE
'Dim wksToSearch As Worksheets

Dim rngToSearch As Range
Dim rngFound As Range

'Set wksToSearch = Sheets("ALTON")
For Each wksToSearch In ThisWorkbook.Worksheets
Set rngToSearch = wksToSearch.Columns("A")
Set rngFound = rngToSearch.Find(What:=TextBox21.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & TextBox21.Value & " was not found."
Else
wksToSearch.Select
rngFound.Select
End If

Next wksToSearch

End Sub

 

[Phillip]

Private Sub Find_Click()
'COMMENTED THIS LINE OF CODE
'Dim wksToSearch As Worksheets

Dim rngToSearch As Range
Dim rngFound As Range

'Set wksToSearch = Sheets("ALTON")
For Each wksToSearch In ThisWorkbook.Worksheets
Set rngToSearch = wksToSearch.Columns("A")
Set rngFound = rngToSearch.Find(What:=TextBox21.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & TextBox21.Value & " was not found."
Else
wksToSearch.Select
rngFound.Select
End If

Next wksToSearch

End Sub











- Show quoted text -

This worked for me

Phillip London UK

Private Sub cmdfind_Click()
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean

FindWhat = TextBox21.Text
Matches = False

For Each sht In Sheets
If TypeName(sht) = "Worksheet" Then
Set rngToSearch = sht.Columns("A")
Set rngFound = rngToSearch.Find(What:=FindWhat, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not (rngFound Is Nothing) Then
Matches = True
sht.Select
rngFound.Select
End If
End If
Next

If Matches = False Then
MsgBox "Sorry " & FindWhat & " was not found."
Else
Me.Tb2.Text = rngFound.Offset(0, 1).Value
Me.tb3.Text = rngFound.Offset(0, 2).Value
Me.tb4.Text = rngFound.Offset(0, 3).Value
End If
End Sub

 

[Hazel]

Hi Phillip

I Had to make one or two adjustments

Me.Tb2.Text = rngFound.Offset(0, 1).Value
Me.tb3.Text = rngFound.Offset(0, 2).Value
Me.tb4.Text = rngFound.Offset(0, 3).Value

Changed rngFound.Offset to ActiveCell.Offset then it stopped the debugger
kicking in.

Just one query with having so many sheets on the odd occasion the ID could
be on two sheets because the area's overlap. Is it possible to just find the
ID on one sheet preferably the first one it finds and ignores any others with
the same ID in the workbook.

Once again Many thanks, its working OK and my gaffer is a happy bunny again.