ID Number Col A

G

Guest

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.
 
G

Guest

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
 
P

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
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

copy and paste a row 16
Find ID Number 10
Stop TextBox Clearing Cell 3
Cut instead of Copy 7
List Box Highlight Selection from Sheet 4
searching on more than one field in VB 5
List Box Match 4
findnext error in loop 2

Top