|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 ***** |
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 ***** |
| All times are GMT +1. The time now is 10:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com