Hi Guys.
At the moment, I have a search spreadsheet where I click a button, and an input box appears where I put a car reg in, and it searches a different sheet, returning results on the row the reg belongs in.
I've had a slight change in spec. I now need the same function to work, but instead of an input box.. I want the user to type the reg in a text box that is already on the sheet, and then click a search button to retrieve the results.
Is there anyway to easily amend the following code to get there?
Sub Button1_Click()
'Sheet module code.
'Find my data in the indicated range, on this sheet!
Dim strMessage$, strTitle$, strDefault$, strShtNm$
Dim lngMyCol&, lngLabelRow&
Dim varRegID As Variant
Dim rngMyData As Range
Dim cell As Object
Application.ScreenUpdating = False
strShtNm = ActiveSheet.Name
strMessage = "Enter ""Reg"" ID, below:" ' Set Prompt.
strTitle = "Find This Information!" ' Set Title.
strDefault = "" ' Set Default.
' Display strMessage, strTitle, and strDefault value.
varRegID = InputBox(strMessage, strTitle, strDefault)
'The row that your data's labels are in!
lngLabelRow = 2
'Change below to your range, the Column to find data in!
With ThisWorkbook.Sheets("Sheet1")
lngLstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
ThisWorkbook.Sheets("Sheet1").Activate
ThisWorkbook.Sheets("Sheet1").Range("A2:A" & lngLstRow).Activate
On Error GoTo myErr
'Find the data!
Selection.Find(What:=varRegID, _
After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'Get row data!
lngMyCol = ActiveCell.Column
lngMyRow = ActiveCell.Row
lngLstCol = Sheets("Sheet1").Cells(lngMyRow, Columns.Count).End(xlToLeft).Column
Set rngMyData = ThisWorkbook.Sheets("Sheet1").Range(Cells(lngMyRow, 1), Cells(lngMyRow, lngLstCol))
For Each cell In rngMyData
lngLabelCol = lngLabelCol + 1
ThisWorkbook.Sheets("Sheet2").Cells(16, lngLabelCol).Value = cell.Value
Next cell
Sheets("Sheet1").Range("A1").Activate
Sheets(strShtNm).Select
GoTo myEnd
myErr:
Sheets("Sheet1").Range("A1").Activate
Sheets(strShtNm).Select
MsgBox "The ""Reg"" ID:" & vbLf & vbLf & _
varRegID & vbLf & vbLf & _
"was not found!", _
vbCritical + vbOKOnly, _
"Search Error!"
myEnd:
Application.ScreenUpdating = True
End Sub
Thanks in advance!!
Bookmarks