Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Find ID Number
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 114
Default Find ID Number
Hi Everybody

I found the code below on this forum and adapted it to my userfrom and it
works great no problems. However with a 26 sheet workbook it always finds the
last ID number in Column A. For example if ID number WM3898 is on Sheets
5,11,23 it will always show in the useform the info on sheet 23. Is it
possible to add a CommandButton that would find the first ID number in the
workbook then on click would find the same ID number if it is within the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.

Private Sub cmdfind_Click()

Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet

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 " & TextBox21 & " was not found."
Else


Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value

TextBox21.Value = ""

End If
Application.ScreenUpdating = True
End Sub



--
Many thanks

hazel
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Find ID Number
What do you want to do with each as you find them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Hazel" wrote in message
...
Hi Everybody

I found the code below on this forum and adapted it to my userfrom and it
works great no problems. However with a 26 sheet workbook it always finds
the
last ID number in Column A. For example if ID number WM3898 is on Sheets
5,11,23 it will always show in the useform the info on sheet 23. Is it
possible to add a CommandButton that would find the first ID number in the
workbook then on click would find the same ID number if it is within the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.

Private Sub cmdfind_Click()

Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet

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 " & TextBox21 & " was not found."
Else


Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value

TextBox21.Value = ""

End If
Application.ScreenUpdating = True
End Sub



--
Many thanks

hazel


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 114
Default Find ID Number
Hi Bob

As each sheet relates to a different area that the agent covers,more than
likely after viewing, print the data in the userform boxes for the our
records and send the agent at the end of each month a hard copy for each area.
--
Many thanks

hazel


"Bob Phillips" wrote:

What do you want to do with each as you find them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Hazel" wrote in message
...
Hi Everybody

I found the code below on this forum and adapted it to my userfrom and it
works great no problems. However with a 26 sheet workbook it always finds
the
last ID number in Column A. For example if ID number WM3898 is on Sheets
5,11,23 it will always show in the useform the info on sheet 23. Is it
possible to add a CommandButton that would find the first ID number in the
workbook then on click would find the same ID number if it is within the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.

Private Sub cmdfind_Click()

Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet

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 " & TextBox21 & " was not found."
Else


Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value

TextBox21.Value = ""

End If
Application.ScreenUpdating = True
End Sub



--
Many thanks

hazel



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Find ID Number
I thought it would be something like that. So after the first is found, and
shown on the form, how do you want to continue to the next?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Hazel" wrote in message
...
Hi Bob

As each sheet relates to a different area that the agent covers,more than
likely after viewing, print the data in the userform boxes for the our
records and send the agent at the end of each month a hard copy for each
area.
--
Many thanks

hazel


"Bob Phillips" wrote:

What do you want to do with each as you find them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Everybody

I found the code below on this forum and adapted it to my userfrom and
it
works great no problems. However with a 26 sheet workbook it always
finds
the
last ID number in Column A. For example if ID number WM3898 is on
Sheets
5,11,23 it will always show in the useform the info on sheet 23. Is it
possible to add a CommandButton that would find the first ID number in
the
workbook then on click would find the same ID number if it is within
the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.

Private Sub cmdfind_Click()

Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet

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 " & TextBox21 & " was not found."
Else


Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value

TextBox21.Value = ""

End If
Application.ScreenUpdating = True
End Sub



--
Many thanks

hazel





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 114
Default Find ID Number
Hi Bob

At the moment we already have a print Button on the Userform with the usual
code

Private Sub CommandButton1_Click()
UserForm1.PrintForm
End Sub

Of course the smart a***s looking over my shoulder think it should be easy
to find out of 500 or so records the agents other records and on which other
sheet they should be on -- they must think I have a memory like an elephant
is supposed to have. Hope this helps

--
Many thanks

hazel


"Bob Phillips" wrote:

I thought it would be something like that. So after the first is found, and
shown on the form, how do you want to continue to the next?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Hazel" wrote in message
...
Hi Bob

As each sheet relates to a different area that the agent covers,more than
likely after viewing, print the data in the userform boxes for the our
records and send the agent at the end of each month a hard copy for each
area.
--
Many thanks

hazel


"Bob Phillips" wrote:

What do you want to do with each as you find them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Everybody

I found the code below on this forum and adapted it to my userfrom and
it
works great no problems. However with a 26 sheet workbook it always
finds
the
last ID number in Column A. For example if ID number WM3898 is on
Sheets
5,11,23 it will always show in the useform the info on sheet 23. Is it
possible to add a CommandButton that would find the first ID number in
the
workbook then on click would find the same ID number if it is within
the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.

Private Sub cmdfind_Click()

Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet

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 " & TextBox21 & " was not found."
Else


Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value

TextBox21.Value = ""

End If
Application.ScreenUpdating = True
End Sub



--
Many thanks

hazel






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Find ID Number
OK. Add another button, I called it cmdNext.

This code will then work by using the find first and it will seek through
until it finds, and stop. You print, press next, and it goes until the end
or next find. You print, etc.

Private shIndex As Long

Private Sub cmdfind_Click()

Application.ScreenUpdating = False

shIndex = 1
SearchForValue

Application.ScreenUpdating = True
End Sub

Private Sub cmdNext_Click()
If shIndex <= Worksheets.Count Then
shIndex = shIndex + 1
SearchForValue
End If
End Sub

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

Set rngFound = Nothing
FindWhat = Me.TextBox21.Text
Do
Set rngToSearch = Worksheets(shIndex).Columns("A")
Set rngFound = rngToSearch.Find(What:=FindWhat, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)

If Not (rngFound Is Nothing) Then
With Me
.Tb2.Text = rngFound.Offset(0, 2).Value
.Tb3.Text = rngFound.Offset(0, 1).Value
.Tb4.Text = rngFound.Offset(0, 4).Value
.Tb5.Text = rngFound.Offset(0, 5).Value
.Tb6.Text = rngFound.Offset(0, 6).Value
.Tb7.Text = rngFound.Offset(0, 7).Value
.Tb8.Text = rngFound.Offset(0, 8).Value
.Tb9.Text = rngFound.Offset(0, 9).Value
.Tb10.Text = rngFound.Offset(0, 10).Value
End With
Else
shIndex = shIndex + 1
End If
Loop Until Not rngFound Is Nothing Or shIndex Worksheets.Count
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Hazel" wrote in message
...
Hi Bob

At the moment we already have a print Button on the Userform with the
usual
code

Private Sub CommandButton1_Click()
UserForm1.PrintForm
End Sub

Of course the smart a***s looking over my shoulder think it should be easy
to find out of 500 or so records the agents other records and on which
other
sheet they should be on -- they must think I have a memory like an
elephant
is supposed to have. Hope this helps

--
Many thanks

hazel


"Bob Phillips" wrote:

I thought it would be something like that. So after the first is found,
and
shown on the form, how do you want to continue to the next?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Bob

As each sheet relates to a different area that the agent covers,more
than
likely after viewing, print the data in the userform boxes for the our
records and send the agent at the end of each month a hard copy for
each
area.
--
Many thanks

hazel


"Bob Phillips" wrote:

What do you want to do with each as you find them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Everybody

I found the code below on this forum and adapted it to my userfrom
and
it
works great no problems. However with a 26 sheet workbook it always
finds
the
last ID number in Column A. For example if ID number WM3898 is on
Sheets
5,11,23 it will always show in the useform the info on sheet 23. Is
it
possible to add a CommandButton that would find the first ID number
in
the
workbook then on click would find the same ID number if it is within
the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.

Private Sub cmdfind_Click()

Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet

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 " & TextBox21 & " was not found."
Else


Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value

TextBox21.Value = ""

End If
Application.ScreenUpdating = True
End Sub



--
Many thanks

hazel








Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 114
Default Find ID Number
Hi Bob

I'm having a problem have tried inserting your code in a new module also
inserting in userform code -- the first line Private shIndex As Long - is
inserting itself at the end of the print form code. Tried many ways to move
it all to no avail. Removed all the other code still no joy. Help???

--
Many thanks

hazel


"Bob Phillips" wrote:

OK. Add another button, I called it cmdNext.

This code will then work by using the find first and it will seek through
until it finds, and stop. You print, press next, and it goes until the end
or next find. You print, etc.


Private Sub cmdfind_Click()

Application.ScreenUpdating = False

shIndex = 1
SearchForValue

Application.ScreenUpdating = True
End Sub

Private Sub cmdNext_Click()
If shIndex <= Worksheets.Count Then
shIndex = shIndex + 1
SearchForValue
End If
End Sub

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

Set rngFound = Nothing
FindWhat = Me.TextBox21.Text
Do
Set rngToSearch = Worksheets(shIndex).Columns("A")
Set rngFound = rngToSearch.Find(What:=FindWhat, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)

If Not (rngFound Is Nothing) Then
With Me
.Tb2.Text = rngFound.Offset(0, 2).Value
.Tb3.Text = rngFound.Offset(0, 1).Value
.Tb4.Text = rngFound.Offset(0, 4).Value
.Tb5.Text = rngFound.Offset(0, 5).Value
.Tb6.Text = rngFound.Offset(0, 6).Value
.Tb7.Text = rngFound.Offset(0, 7).Value
.Tb8.Text = rngFound.Offset(0, 8).Value
.Tb9.Text = rngFound.Offset(0, 9).Value
.Tb10.Text = rngFound.Offset(0, 10).Value
End With
Else
shIndex = shIndex + 1
End If
Loop Until Not rngFound Is Nothing Or shIndex Worksheets.Count
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Hazel" wrote in message
...
Hi Bob

At the moment we already have a print Button on the Userform with the
usual
code

Private Sub CommandButton1_Click()
UserForm1.PrintForm
End Sub

Of course the smart a***s looking over my shoulder think it should be easy
to find out of 500 or so records the agents other records and on which
other
sheet they should be on -- they must think I have a memory like an
elephant
is supposed to have. Hope this helps

--
Many thanks

hazel


"Bob Phillips" wrote:

I thought it would be something like that. So after the first is found,
and
shown on the form, how do you want to continue to the next?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Bob

As each sheet relates to a different area that the agent covers,more
than
likely after viewing, print the data in the userform boxes for the our
records and send the agent at the end of each month a hard copy for
each
area.
--
Many thanks

hazel


"Bob Phillips" wrote:

What do you want to do with each as you find them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Everybody

I found the code below on this forum and adapted it to my userfrom
and
it
works great no problems. However with a 26 sheet workbook it always
finds
the
last ID number in Column A. For example if ID number WM3898 is on
Sheets
5,11,23 it will always show in the useform the info on sheet 23. Is
it
possible to add a CommandButton that would find the first ID number
in
the
workbook then on click would find the same ID number if it is within
the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.

Private Sub cmdfind_Click()

Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet

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 " & TextBox21 & " was not found."
Else


Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value

TextBox21.Value = ""

End If
Application.ScreenUpdating = True
End Sub



--
Many thanks

hazel









Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Find ID Number
It should go in the Userform code module.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Hazel" wrote in message
...
Hi Bob

I'm having a problem have tried inserting your code in a new module also
inserting in userform code -- the first line Private shIndex As Long -
is
inserting itself at the end of the print form code. Tried many ways to
move
it all to no avail. Removed all the other code still no joy. Help???

--
Many thanks

hazel


"Bob Phillips" wrote:

OK. Add another button, I called it cmdNext.

This code will then work by using the find first and it will seek through
until it finds, and stop. You print, press next, and it goes until the
end
or next find. You print, etc.


Private Sub cmdfind_Click()

Application.ScreenUpdating = False

shIndex = 1
SearchForValue

Application.ScreenUpdating = True
End Sub

Private Sub cmdNext_Click()
If shIndex <= Worksheets.Count Then
shIndex = shIndex + 1
SearchForValue
End If
End Sub

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

Set rngFound = Nothing
FindWhat = Me.TextBox21.Text
Do
Set rngToSearch = Worksheets(shIndex).Columns("A")
Set rngFound = rngToSearch.Find(What:=FindWhat, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)

If Not (rngFound Is Nothing) Then
With Me
.Tb2.Text = rngFound.Offset(0, 2).Value
.Tb3.Text = rngFound.Offset(0, 1).Value
.Tb4.Text = rngFound.Offset(0, 4).Value
.Tb5.Text = rngFound.Offset(0, 5).Value
.Tb6.Text = rngFound.Offset(0, 6).Value
.Tb7.Text = rngFound.Offset(0, 7).Value
.Tb8.Text = rngFound.Offset(0, 8).Value
.Tb9.Text = rngFound.Offset(0, 9).Value
.Tb10.Text = rngFound.Offset(0, 10).Value
End With
Else
shIndex = shIndex + 1
End If
Loop Until Not rngFound Is Nothing Or shIndex Worksheets.Count
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Bob

At the moment we already have a print Button on the Userform with the
usual
code

Private Sub CommandButton1_Click()
UserForm1.PrintForm
End Sub

Of course the smart a***s looking over my shoulder think it should be
easy
to find out of 500 or so records the agents other records and on which
other
sheet they should be on -- they must think I have a memory like an
elephant
is supposed to have. Hope this helps

--
Many thanks

hazel


"Bob Phillips" wrote:

I thought it would be something like that. So after the first is
found,
and
shown on the form, how do you want to continue to the next?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Bob

As each sheet relates to a different area that the agent covers,more
than
likely after viewing, print the data in the userform boxes for the
our
records and send the agent at the end of each month a hard copy for
each
area.
--
Many thanks

hazel


"Bob Phillips" wrote:

What do you want to do with each as you find them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"Hazel" wrote in message
...
Hi Everybody

I found the code below on this forum and adapted it to my
userfrom
and
it
works great no problems. However with a 26 sheet workbook it
always
finds
the
last ID number in Column A. For example if ID number WM3898 is on
Sheets
5,11,23 it will always show in the useform the info on sheet 23.
Is
it
possible to add a CommandButton that would find the first ID
number
in
the
workbook then on click would find the same ID number if it is
within
the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.

Private Sub cmdfind_Click()

Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet

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 " & TextBox21 & " was not found."
Else


Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value

TextBox21.Value = ""

End If
Application.ScreenUpdating = True
End Sub



--
Many thanks

hazel











Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 114
Default Find ID Number
Hi Bob

As usual when we do what you tell us to do and do it properly everything
works beautifully -- much appreciated your time and effort ---- now the
smart ***** will leave me alone to get on with my proper day job.
--
Many thanks

hazel


"Bob Phillips" wrote:

It should go in the Userform code module.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Hazel" wrote in message
...
Hi Bob

I'm having a problem have tried inserting your code in a new module also
inserting in userform code -- the first line Private shIndex As Long -
is
inserting itself at the end of the print form code. Tried many ways to
move
it all to no avail. Removed all the other code still no joy. Help???

--
Many thanks

hazel


"Bob Phillips" wrote:

OK. Add another button, I called it cmdNext.

This code will then work by using the find first and it will seek through
until it finds, and stop. You print, press next, and it goes until the
end
or next find. You print, etc.


Private Sub cmdfind_Click()

Application.ScreenUpdating = False

shIndex = 1
SearchForValue

Application.ScreenUpdating = True
End Sub

Private Sub cmdNext_Click()
If shIndex <= Worksheets.Count Then
shIndex = shIndex + 1
SearchForValue
End If
End Sub

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

Set rngFound = Nothing
FindWhat = Me.TextBox21.Text
Do
Set rngToSearch = Worksheets(shIndex).Columns("A")
Set rngFound = rngToSearch.Find(What:=FindWhat, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)

If Not (rngFound Is Nothing) Then
With Me
.Tb2.Text = rngFound.Offset(0, 2).Value
.Tb3.Text = rngFound.Offset(0, 1).Value
.Tb4.Text = rngFound.Offset(0, 4).Value
.Tb5.Text = rngFound.Offset(0, 5).Value
.Tb6.Text = rngFound.Offset(0, 6).Value
.Tb7.Text = rngFound.Offset(0, 7).Value
.Tb8.Text = rngFound.Offset(0, 8).Value
.Tb9.Text = rngFound.Offset(0, 9).Value
.Tb10.Text = rngFound.Offset(0, 10).Value
End With
Else
shIndex = shIndex + 1
End If
Loop Until Not rngFound Is Nothing Or shIndex Worksheets.Count
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Bob

At the moment we already have a print Button on the Userform with the
usual
code

Private Sub CommandButton1_Click()
UserForm1.PrintForm
End Sub

Of course the smart a***s looking over my shoulder think it should be
easy
to find out of 500 or so records the agents other records and on which
other
sheet they should be on -- they must think I have a memory like an
elephant
is supposed to have. Hope this helps

--
Many thanks

hazel


"Bob Phillips" wrote:

I thought it would be something like that. So after the first is
found,
and
shown on the form, how do you want to continue to the next?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Hazel" wrote in message
...
Hi Bob

As each sheet relates to a different area that the agent covers,more
than
likely after viewing, print the data in the userform boxes for the
our
records and send the agent at the end of each month a hard copy for
each
area.
--
Many thanks

hazel


"Bob Phillips" wrote:

What do you want to do with each as you find them?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"Hazel" wrote in message
...
Hi Everybody

I found the code below on this forum and adapted it to my
userfrom
and
it
works great no problems. However with a 26 sheet workbook it
always
finds
the
last ID number in Column A. For example if ID number WM3898 is on
Sheets
5,11,23 it will always show in the useform the info on sheet 23.
Is
it
possible to add a CommandButton that would find the first ID
number
in
the
workbook then on click would find the same ID number if it is
within
the
workbook, e.g. WM3898 Sheet5, then Sheet11, Sheet23.

Private Sub cmdfind_Click()

Application.ScreenUpdating = False
Dim rngToSearch As Range
Dim rngFound As Range
Dim FindWhat As String
Dim Matches As Boolean
Dim sht As Worksheet

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 " & TextBox21 & " was not found."
Else


Me.Tb2.Text = ActiveCell.Offset(0, 2).Value
Me.Tb3.Text = ActiveCell.Offset(0, 1).Value
Me.Tb4.Text = ActiveCell.Offset(0, 4).Value
Me.Tb5.Text = ActiveCell.Offset(0, 5).Value
Me.Tb6.Text = ActiveCell.Offset(0, 6).Value
Me.Tb7.Text = ActiveCell.Offset(0, 7).Value
Me.Tb8.Text = ActiveCell.Offset(0, 8).Value
Me.Tb9.Text = ActiveCell.Offset(0, 9).Value
Me.Tb10.Text = ActiveCell.Offset(0, 10).Value

TextBox21.Value = ""

End If
Application.ScreenUpdating = True
End Sub



--
Many thanks

hazel












Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Find ID Number
"Hazel" wrote in message
...
Hi Bob

As usual when we do what you tell us to do and do it properly everything
works beautifully -- much appreciated your time and effort ---- now the
smart ***** will leave me alone to get on with my proper day job.


Not a chance. They have to let you know that they are still smart, they just
don't know they are *****


Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 114
Default Find ID Number
As usual you have Nailed it on the Head!! they are at me now for me to do
more stuff its starting to get embarassing already -- told 'em to get lost
will have a look next week.
--
Many thanks

hazel


"Bob Phillips" wrote:

"Hazel" wrote in message
...
Hi Bob

As usual when we do what you tell us to do and do it properly everything
works beautifully -- much appreciated your time and effort ---- now the
smart ***** will leave me alone to get on with my proper day job.


Not a chance. They have to let you know that they are still smart, they just
don't know they are *****



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find number of weekdays and wekend days given a total number of da Juan Correa Excel Discussion (Misc queries) 7 February 19th 10 09:47 PM
Find previous number and find next number in column DoubleZ Excel Discussion (Misc queries) 4 March 4th 09 08:51 PM
find an exact number in a different sheet and use the cell containing the number to output information [email protected] Excel Programming 4 February 18th 07 08:59 PM
countif formula to find the occurances of a number that is greater than one number but less than another steveo Excel Discussion (Misc queries) 3 July 8th 06 02:04 AM
find and delete text, find a 10-digit number and put it in a textbox Paul Excel Programming 3 November 16th 04 04:21 PM


All times are GMT +1. The time now is 05:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement