VBA *Wildcard* VLookup - is it possible?

[LORDMARKS]

Hi All

I am trying to write a code to analyse a column and lookup each value in turn on a seperate table. I would like it to look at the value (text comment) and if it contains a key word in a lookup table return the corresponding value and input it into the adjacent column.

So far my code will look for exact match of the cooment vs the value in the lookup table, but I really need it to be a wildcard search.

Sub LookUpComments() 'exact match only ???
On Error Resume Next
Application.ScreenUpdating = False
Dim DataRow As Long
Dim DataClm As Long
Dim Result As Variant
DataTable = Sheet3.Range("D5:D35")
LookUpTable = Sheet3.Range("AA10:AB20")
Sheet3.Range("E5:E10000").ClearContents
DataRow = Sheet3.Range("E5").Row
DataClm = Sheet3.Range("E5").Column
For Each cl In DataTable
              '''Sheet3.Cells(DataRow, DataClm) = Application.WorksheetFunction.VLookup("*" & cl & "*", LookUpTable, 2, blnLookupType) 'try 2
    Result = Application.WorksheetFunction.VLookup("*" & cl & "*", "*" & LookUpTable & "*", 2, blnLookupType)
    If Result = Error Then GoTo E
    Sheet3.Cells(DataRow, DataClm) = Result
              '''Sheet3.Cells(DataRow, DataClm) = Application.WorksheetFunction.VLookup(cl, LookUpTable, 2, True) 'try 1
E:    DataRow = DataRow + 1
Next cl
Application.ScreenUpdating = True
MsgBox "Data LookUp is complete"
End Sub

If this is not possible, could it be done using some kind of search function and offset?
I am also having trouble with the error handling?

Thanks in advance.

 

[CharlesChuckieCharles]

You may be better off using the .Find .FindNext functions

<CODE>With Worksheets(1).Range("a1:a500") 
Set c = .Find(2, lookin:=xlValues) 
If Not c Is Nothing Then firstAddress = c.Address 
Do 
c.Value = 5 
Set c = .FindNext(c) 
Loop While Not c Is Nothing And c.Address <> firstAddress 
End If
End With</CODE>

</PRE>