Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find number of weekdays and wekend days given a total number of da | Excel Discussion (Misc queries) | |||
Find previous number and find next number in column | Excel Discussion (Misc queries) | |||
find an exact number in a different sheet and use the cell containing the number to output information | Excel Programming | |||
countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming |