excel - vba match function case sensivity

Question

Im working with 200 files, where I have to open each of them, find specific column and enter data in this column.

The problem is that in these files sometimes I have column header like Local Dividend with capital letters, but sometimes I have local dividend with lower case letters. There are also files where one is capital and one is not eg. Local dividend.

The problem is that when I use application.match("Local Dividend",range("A1:AD1"),0) I will get a match only when the text is the same (with capital letters).

I tried Option Compare Text but with match it does not working.

Is there a way/code which can help avoid comparing capital/lower case letters in MATCH?

match in cell as formula Thanks

question from:https://stackoverflow.com/questions/65915388/vba-match-function-case-sensivity

Answer 1

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