I found a similar question in a different post called "In Excel I need to find data matches between two arrays - one horizontal and one vertical" and I found part of my answer there. Unfortunately, I did not see how I could add a sub-question to this post. So here is the case.
I have an Excel workbook with two sheets:
1) In Sheet "Vendors" in column A, I listed all my vendor names (around 40).
2) In Sheet "Data" I have different data filled from column B until column Z. In this "Data" sheet in column F and/or column L the vendor name is listed for each row BUT the vendor name is embedded in more text. This text is never the same. It could be the vendor name with a space followed by numbers or the vendor name followed by a dash without space in between and then followed by numbers (e.g. "Vendor name-233" or "Vendor name / gfjd").
What I need the VBA to do is to use the vendor names in cloumn A of the "Vendor" sheet and to match it with the vendor names that are contained in the text strings of columns F and/or L of the "Data" sheet. The VBA should then return the found vendor name for each row in column A of the "Data" sheet.
This is the VBA that I tried from the previous post. The big problem is that it would only return the vendor name if the exact value is found in the "Data" sheet. The VBA cannot work with the vendor name embedded in the text string.
Sub newtest() Dim data, reference As Range Dim skipsome As Boolean skipsome = False
Set reference = Worksheets("Vendors").Range("A1", "A40")
Set data = Worksheets("Data").Range("B2", "F6")
For Each dataCell In data
For Each referenceCell In reference
If dataCell.Value = referenceCell.Value Then
Worksheets("Data").Cells(dataCell.Row, 1).Value = dataCell.Value
skipsome = True
Exit For
End If
If skipsome = True Then
skipsome = False
Exit For
End If
Next
Next
End Sub
I would really appreciate if you could help me.
Thanks, Pia
