VBA search 3 partial criteria and display results

dmj120

Board Regular
Joined
Jan 5, 2010
Messages
194
Office Version
  1. 2019
  2. 2010
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
ManfGE
Device
Mdl #52


ManfDeviceMdl#
3Gen Inc.52 F
AerogenCDL 1521
GE Appliances/Hotpoint56077852
 

dmj120

Board Regular
Joined
Jan 5, 2010
Messages
194
Office Version
  1. 2019
  2. 2010
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
1624310372855.png


VBA Code:
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,572
Office Version
  1. 2007
Platform
  1. Windows

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?
 

Forum statistics

Threads
1,141,062
Messages
5,704,060
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top