Checkword macro multiple results

[MichaelGood]

Hi there,
I am trying to set up a search function for a list of approved chemicals based on a partial search criteria using Checkword macro which seems to work quite well in providingmultiple results.

However, when there is no search criteria (empty box) itreturns every result rather than no result.
Any ideas on how to tweak this would be appreciated.

Macro:
Function CheckWords(rng As Range,lookupwords As String, Optional num As Long = 1) As String

Dimlookup() As String
Dimi As Long, Ctr As Long

Dimc As Range

DimbPassed As Boolean



lookup = Split(lookupwords)

ForEach c In rng

bPassed = True
For i = 0 To UBound(lookup)

If InStr(1, " " & c.Value, " " & lookup(i),vbTextCompare) = 0 Then

bPassed = False

Exit For

End If

Next i
IfbPassed Then Ctr = Ctr + 1
IfCtr = num Then
CheckWords = c.Value
Exit For
End If
Nextc
End Function

Cell D2 =CheckWords(A$2:A$71,D$1,ROWS(D$2:D2))

 

[CharlesChuckieCharles]

Why Not try

Dim bPassed as Boolean

If Trim(lookupwords)="" then
    Checkwords=""
    Exit Function
EndIf