Announcement

Collapse
No announcement yet.

vba code for case insensitive comparision

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • vba code for case insensitive comparision

    Hi All,

    Can anyone help me to write a vba code to compare two strings. Ignore case sensitivity.

    Many Thanks,

  • #2
    Re: vba code for case insensitive comparision

    here you go:

    Code:
    Sub compstring()
    x = UCase(Range("A1").Value)
    y = UCase(Range("A2").Value)
    If x = y Then
    MsgBox "they match"
    Else
    MsgBox "they DON'T match"
    End If
    End Sub
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Comment


    • #3
      Re: vba code for case insensitive comparision

      Thanks h1h, is there a way to compare without doing any case conversions

      Comment


      • #4
        Re: vba code for case insensitive comparision

        could be, but what for. The conversion is only done within the code for comparison. the actual values are not touched.
        In formulae, depending on your locale, you might have to replace ; with , or vice versa.

        Comment


        • #5
          Re: vba code for case insensitive comparision

          Have a look at the StrComp() function

          Cheers
          Andy

          Comment


          • #6
            Re: vba code for case insensitive comparision

            Put Option Compare Text at the top of the code.
            Boo!

            Comment


            • #7
              Re: vba code for case insensitive comparision

              Hey guys,

              im new to the forum but i got the same(or similar) problem as Sri. I have tried both the StrComp and Option Compare Text, but both dont seem to work for me. Please do take a look at my code, thanks!

              Note: I did a search function which found the first entry of the desired string in a range of cells, but the moment i changed all to uppercase, it could no longer find the cell which contained the desired string.


              StrComp Usage (SearchCriteria is a String that = "Gerry"):
              Code:
               
              For I = 4 To 500
                    If ThisSheet.Cells(I, 2) = StrComp(ThisSheet.Cells(I, 2).Text, SearchCriteria, vbTextCompare = 0) Then
                    ColPosition = I
                    MsgBox "First Entry of Gerry in Cell " + RowPosition + ColPosition, vbOKCancel
                    IsFound = True
                    End If
              Next I

              Option Compare Text Usage (I really don't know how to use it):
              Code:
              Sub CompareText()
              Dim TestCell As Range
                  For Each TestCell In Range("A1:B500")
                      If UCase(TestCell) = "Gerry" Then
                          MsgBox TestCell.Address & " has " & TestCell & " in it"
                      End If
                  Next rCell
              End Sub
              Sub Test1()
              'rest of my other coding goes here
              End Sub

              Comment


              • #8
                Re: vba code for case insensitive comparision

                Sorry the 2nd Coding is a mistake. This is the correct one for Option Compare Text.

                Code:
                Sub OptionCompareText()
                Dim TestCell As Range
                    For Each TestCell In Range("A1:B500")
                        If TestCell = "Gerry" Then
                            MsgBox TestCell.Address & " has " & TestCell & " in it"
                        End If
                    Next TestCell
                End Sub
                
                Sub Test1()
                'rest of my other coding goes here
                End Sub
                Thanks a lot!!

                Comment


                • #9
                  Re: vba code for case insensitive comparision

                  Have you considered actually using the Find method?

                  One of it's arguments specifies whether or not the search is case-sensitive.
                  Boo!

                  Comment

                  Working...
                  X