Lookup Partial String To Return Data From Corresponding Columns and Rows (VBA)

Question

I have a client/job database for which I would like to be able to do partial and full searches. This database includes at least 19 different pieces of data, e.g. Company Name, Company Address, Point-Of-Contact, job start and end date, job revenue etc..

I have a single search field, where I can enter different column inputs and search for the relevant client. For example, If I key in Client Name, it will return all jobs with that client. If I search 201X, it will return all jobs done in 201X. This returned data is populated as a list below my search box.

My current problem is I am unable to do partial searches. E.g. I must type in 'General Electric', as opposed to just 'Electric'. The VBA code I've used is provided below. Any help will be greatly appreciated!

Option Compare Text 
Sub SearchClientRecord()

Dim Search As String 
Dim Finalrow As Integer 
Dim SearchFinalRow As Integer 
Dim i As Integer 
Dim scs As Worksheet 
Dim scd As Worksheet

Set scs = Sheets("Client Search") 
Set scd = Sheets("Client Database")

scs.Range("C19:S1018").ClearContents

Search = scs.Range("C12") 
Finalrow = scd.Range("D100000").End(xlUp).Row 
SearchFinalRow = scs.Range("D100000").End(xlUp).Row

For j = 3 To 19 
For i = 19 To Finalrow

If scd.Cells(i, j) Like Search Then 
scd.Range(scd.Cells(i, 3), scd.Cells(i, 19)).Copy
scs.Range("C100000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats 
End If 
Next i 
Next j 
scs.Range("C19:S1018").Select
    scs.Range("$C$18:$S$1009").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _ , 7), Header:=xlYes

Call Border Columns("C:S").HorizontalAlignment = xlCenter

End Sub 
Sub AddJobDetail() 
JobDetailForm.Show 
End Sub

You need wildcards with your Like: If scd.Cells(i, j) Like "*" & Search & "*" Then — Rory
– Rory, Commented Mar 21, 2016 at 11:21

Absinthe · Accepted Answer · 2016-03-21 12:14:55Z

2

Use the Instr function to find one string within another - https://msdn.microsoft.com/en-us/library/8460tsh1(v=vs.90).aspx

e.g. replace

If scd.Cells(i, j) Like Search Then

with

if Instr(scd.Cells(i,j), Search) > -1 then

But of course be careful typing 'Electric' if you have 'General Electric' and 'Electric Motors' :)

answered Mar 21, 2016 at 12:14

Absinthe

3,4218 gold badges37 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Lookup Partial String To Return Data From Corresponding Columns and Rows (VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related