TextBox - Named Range Dynamic Search

[davis1118]

I have been struggling to get a search function to correctly work. I might be going at this the wrong way, but it’s the only way I could think of to make it work like I need.
I have a sheet that has a list of part numbers in column O,but there are also 12 other hidden columns that contain other part numbers that relate to the visible column “O”. The part numbers in column “O” are hyperlinked to the related sheet for that part number.

I want to be able to type any part number in a textbox and find the rowthat the part number is in, then combine the row number and the column “O” to get the cell that the corresponding hyperlink part number is in. Then, either copy and paste the cell with the hyperlink into another text box so the user can click on it and be directed to the correct sheet, or just follow the hyperlink directly to the corresponding sheet.

I am able to get the searched cell row number, and the column “O” as variables,but I keep getting an “application defined or object defined error” on the .Cells line.

Again, this was the easiest way I could think of to dynamically search an entire named range and pull out the hyperlink cell in column "O". Using filters limited me to only searching one column at a time,or at least that’s the only way I know how to use the filter function. Below is the code I have so far.


Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim rgFound As Range
Dim txt As String
Dim myrow As String
Dim srchrng As Range
Dim mycolumn As String

If KeyCode = vbKeyReturn Then

txt = TextBox1.Value
Set rgFound = Range("partnumbers").Find(txt)
myrow = rgFound.Row
Set srchrng = Range("columnO")
mycolumn = srchrng.Column

With ThisWorkbook.Worksheets("Product Documents")
If TextBox1.Value = "" Then
MsgBox "Please enter number to search"
Else
.Cells(myrow, mycolumn).Copy .Range("Q20")

End If
End With
End If
End Sub

Thank you for the help.

[davis1118]

So I had more time to play around with this today, and figured out how to make it work. I had a few things mixed up and had to add another variable. But now I can search through the entire named range with the textbox, find the row of the searched part number or name, and then select the sheet that corresponds with the part number in column O of that row. Below is my working code. Maybe not the nicest code but it works well so far.


Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Dim rgFound As Range
Dim txt As String
Dim myrow As String
Dim mycolumn As Integer
Dim mycell As Range
Dim sht As String


If KeyCode = vbKeyReturn Then


With Sheet30

If .TextBox1.Value = "" Then
Exit Sub
Else
On Error GoTo ErrHandler
txt = .TextBox1.Value
Set rgFound = .Range("PartSearch").Find(txt)
myrow = rgFound.Row
mycolumn = 17
Set mycell = .Cells(myrow, mycolumn)
sht = mycell.Value

Sheets(sht).Select
.TextBox1 = Empty

End If
End With
End If
Exit Sub
ErrHandler:
MsgBox "Part Not Found", vbExclamation, "Not Found"
Sheet30.TextBox1 = Empty
End Sub