Can someone please help me with finding the row value after matching a string to string values in a column? When I use the MATCH function to look for exact match of a numeric value, my script runs just fine but then when I switch the numeric value to a string the MATCH function doesn't seem to return any values for the row numbers. Here's my working code for the specific numeric value:
Dim detailed As Worksheet Dim lRow As Long FRP = Sheets("Import").Range("B4") On Error Resume Next lRow = Application.WorksheetFunction.Match(FRP, Sheets("Detailed").Range("B:B"), 0) On Error GoTo 0
I get correct row numbers from this, but when I switch this to a string as follows:
Dim detailed As Worksheet Dim lRow As Long Dim ShortFileName As String ShortFileName = Left(fileName, (Len(fileName) - 5)) On Error Resume Next lRow = Application.WorksheetFunction.Match(ShortFileName, Sheets("Detailed").Range("C:C"), -1) On Error GoTo 0
The above "On Error" parts are nested inside a loop to read through a list of workbooks in a specific directory.
I'm kind of at a loss on how to get the row value on this. The string is a close match, not an exact match. For example, the lookup value might be "A&Z Corp" but the column (Sheets("Detailed").Range("C:C")) actually has it as "A&Z Pharmacy", or it could be something like "Steward" as the lookup value but the column might contain "Steward Health Systems" etc. Is the MATCH function basically too simple to what I'm trying to do? Should I be trying to look for more involved ways of looking up fuzzy string matchings? Any help here would be much appreciated.