Test for Upper Case and Lower Case, VBA.

[ikjohnson]

Hi,

I'd like to test the text of column headings in an Excel spreadsheet, where the headings may vary slightly from one data source to another.
For example, one worksheet may contain the heading Dealer in C4, where another worksheet may use Dealer Name. In the code below, the Left function seems to handle this situation.

I'm wondering how to handle situations where the heading in A4 could be either TERRITORY or Territory? (Both cases would be acceptable.)

Is there also a way to test dates? eg. D4 on occasions may contain 1/31/09 or 31/1/09, but formatted as Jan-09, and on other occasions just the text Jan. Again, both cases would be acceptable.

Sub TestHeadings()

    Dim strAnswer As String

    ' Test Headings here
    If Range("A4").Value <> "Terr" _
       Or Left(Range("B4").Value, 3) <> "Acc" _
       Or Left(Range("C4").Value, 6) <> "Dealer" _
       Or Range("D4").Value <> "Jan" Then

        strAnswer = MsgBox("The worksheet " & ActiveSheet.Name & " may have headings that do not match previous data." _
                           & vbCrLf & vbCrLf & _
                           "The data import will stop at this point." _
                           & vbCrLf & _
                           "Please recheck this worksheet.", _
                           vbOKOnly & vbInformation, "This data may have incorrect headings...")

        If strAnswer = vbOK Then
            Exit Sub
        End If
    End If

End Sub

 

[RoryA]

You can use:

If UCase$(Range("A4").Value) <> "TERR" _

and so on.

 

[Andrew Poulsom]

Try:

If UCase(Range("A4").Value) <> "TERR" _
   Or UCase(Left(Range("B4").Value, 3)) <> "ACC" _
   Or UCase(Left(Range("C4").Value, 6)) <> "DEALER" _
   Or UCase(Range("D4").Value) <> "JAN" Then

 

[ikjohnson]

Thanks guys! Both solutions do the job.