VBA search 3 partial criteria and display results

[dmj120]

In an attempt to streamline, I'm wondering if there's a way to add a "search feature" to a sizable workbook.

I'm looking for help with a project (I know VERY little VBA). I'd like to have two tabs: tab 1 the dataset, tab 2 the 'search criteria and results listed.'

Tab 1 has the same three columns as below - just 50k+ rows.

On tab 2, I want to have the ability to enter partial names/numbers into the "search criteria," and then have the results listed in a table below.
A few other points:
1. Not all three search fields will be used each time
2. each field needs to search as a the corresponding cell as a sting omitting spaces, dashes and slashes

The idea is to find a "short list" of possible items (Manf/device/mdl#) that will then be copied onto a different file for consistent nomenclature and further processing. Using the main dataset's filters work to a point, but I'm hoping for something better.

Below are a couple sample representations of what I'm trying to do.

Search Criteria
Manf	GE
Device
Mdl #	52

Manf	Device	Mdl#
3Gen Inc.		52 F
Aerogen		CDL 1521
GE Appliances/Hotpoint		56077852

 

[dmj120]

I'm still lost, what is the problem?

This search code works great! I'm wondering if it can be tweaked a bit so spaces, dashes, etc. in the "mdl# search can be ignored and still find the correct item - which are all standardized to replace slashes, dashes, periods, with a 'space' and a space is added between a letter and number.

Example:
In the mdl# I enter L-1.4

and this screenshot is the results

Sub Search_criteria()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long
  Dim cad1 As String, cad2 As String, cad3 As String
  '
  Set sh1 = Sheets("MCT")
  Set sh2 = Sheets("search")
  '
  a = sh1.Range("A1:C" & sh1.Range("A:C").Find("*", , xlValues, , 1, 2).Row).Value2
  sh2.Range("A8:C" & Rows.Count).ClearContents
  ReDim b(1 To UBound(a, 1), 1 To 3)
  
  If sh2.[B2] <> "" Then cad1 = "*" & LCase(sh2.[B2].Value) & "*"
  If sh2.[B3] <> "" Then cad2 = "*" & LCase(sh2.[B3].Value) & "*"
  If sh2.[B4] <> "" Then cad3 = "*" & LCase(sh2.[B4].Value) & "*"
  
  For i = 1 To UBound(a, 1)
    If LCase(a(i, 1)) Like cad1 And LCase(a(i, 2)) Like cad2 And LCase(a(i, 3)) Like cad3 Then
      j = j + 1
      b(j, 1) = a(i, 1)
      b(j, 2) = a(i, 2)
      b(j, 3) = a(i, 3)
    End If
  Next i
  
  If j > 0 Then sh2.Range("A8").Resize(j, 3).Value = b
  
'moves to top of displayed list
  Range("A8").Select
  

 
End Sub

 

[DanteAmor]

L-1.4

That is a new topic, I recommend you create a new thread. That way you would get help from more experts.
Put several examples of the conversions you want, that is, you have this
L-1.4
and you want the result:
L 14

But to find a pattern you must put several examples.
For example:
15M18
Result?

Other example
S5-18
Result?

Or
3.15K
Result?