Setting a search range to whatever the active sheet is

I am making a search userform that looks at a specified column in the active worksheet and returns the values within that row.

It works great on the first sheet (I originally had it searching just one worksheet so I was able to specify that specific sheet). But I wanted to make one generic userform for the entire workbook. Simply put I want the userform to search within whatever worksheet the button that activates the form (i.e. If the button is pressed in Sheet1 it should search within sheet1). I am getting an error with setting the search range.


My current idea for this was to set the active worksheet upon userform initialization. Here is the code:

Private Sub UserForm_Initialize()

Dim Active As Worksheet
Set Active = ThisWorkbook.ActiveSheet

End Sub

The user types in what they are searching for and presses search. Here is the search command button code:

Private Sub cmdsearch_Click()
Dim Search As String
Dim FoundCell As Range, SearchRange As Range

'This is where I am getting the error (Subscript Out Of Range). I know it has something to do with the active worksheet. '
'This is what I have now. I have tried just doing Active.Column(4) and ActiveSheet.Column(4) as well'
Set SearchRange = Worksheets("Active").Column(4)


Search = Me.txtfind.Text

If Len(Search) = 0 Then Exit Sub

Set FoundCell = SearchRange.Find(What:=Search, LookIn:=xlValues, LookAt:=xlPart _
, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
Me.txtticket.Text = FoundCell.value
Me.txtproject.Text = FoundCell.Offset(0, 1).value
Me.txtdate.value = FoundCell.Offset(0, 2).value
Me.txttube.value = FoundCell.Offset(0, -1).value
Else

MsgBox Search & Chr(10) & "Ticket ID Not Found", 48, "Not Found"
End If
End Sub


It's early in the morning and maybe I'm just not thinking clearly.

Try this.

Please Login or Register  to view this content.

You definitely used Columns when you got that error?