Trailing spaces can also be a source of grief.
The following is a simple match function which accepts a range and a value as parameters and returns the column number (relative to the range offset), and returns zero if not found. It is case-insensitive and strips leading and trailing spaces in the comparison.
You could maybe try it instead of using Application.Match
Function MatchString(sText As String, rg As Range) As Integer
Dim vCell As Range
MatchString = 0
For Each vCell In rg
If LCase(Trim(vCell.Text)) = LCase(Trim(sText)) Then
MatchString = vCell.Column + 1 - rg.Column
Exit For
End If
Next
End Function
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…