Search Engine Macro on Excel Not Searching

[msdaniellew]

Hi everyone!

Recently, I joined a new glass railing company, and the first thing they had me doing was creating an Excel sheet that works like a search engine. This would allow users to search certain measurements (height, width, hole to hole diameter), and the criteria would provide the row that all of the information is in, which included the panel number of the pane of glass, as well as a few other options such as panels that are 1"-2" less, or, 1"-2" more.

I successfully got all of the formulas to work, or so I thought.

For whatever reason, and I can't figure out why, my formulas/macro/whatever will not pull up results after row #20. Everything else will work, say I have "height = 42, width = 12, hole to hole diameter = 0" and it would show row #10. 1" less would show row #20, 2" less would show row #14, 2" more would show row #18, and 1" more would show "#N/A" because the row with that specific measurement is in row #22.

I tried manually inserting a few rows, hoping it would keep all my formulas together and help bump up the range but that didn't work.

If anyone has any suggestions at all, it would be greatly appreciated!

 

[grawls44]

Hi,
If you could post the code that would help everyone to see what is going on with it.

 

[msdaniellew]

Hi,
If you could post the code that would help everyone to see what is going on with it.

Duh, thank you!

For the search bar, my macro is:

Sub SearchBox()
'PURPOSE: Filter Data on User-Determined Column & Text/Numerical value
'SOURCE: www.TheSpreadsheetGuru.com


Dim myButton As OptionButton
Dim SearchString As String
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant


'Load Sheet into A Variable
Set sht = ActiveSheet


'Unfilter Data (if necessary)
On Error Resume Next
sht.ShowAllData
On Error GoTo 0

'Filtered Data Range (include column heading cells)
Set DataRange = sht.Range("B8:B35") 'Cell Range
'Set DataRange = sht.ListObjects("Table1").Range 'Table


'Retrieve User's Search Input
mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
'mySearch = sht.Range("A1").Value 'Cell Input

'Determine if user is searching for number or text
If IsNumeric(mySearch) = True Then
SearchString = "=" & mySearch
Else
SearchString = "=*" & mySearch & "*"
End If

'Loop Through Option Buttons
For Each myButton In sht.OptionButtons
If myButton.Value = 1 Then
ButtonName = myButton.Text
Exit For
End If
Next myButton

'Determine Filter Field
On Error GoTo HeadingNotFound
myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
On Error GoTo 0

'Filter Data
DataRange.AutoFilter _
Field:=myField, _
Criteria1:=SearchString, _
Operator:=xlAnd

'Clear Search Field
sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
'sht.Range("A1").Value = "" 'Cell Input

Exit Sub


'ERROR HANDLERS
HeadingNotFound:
MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"

End Sub

For the clear button:

Sub ClearFilter()
'PURPOSE: Clear all filter rules


'Clear filters on ActiveSheet
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

End Sub

And my formula for the search criteria is:

{=INDEX(C9:F28,MATCH(1,(D:D=J2)*(E:E=J3)*(F:F=J4),0),C9:C28)}