Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: vba code for case insensitive comparision

  1. #1
    Join Date
    19th October 2005
    Posts
    18

    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,

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    Re: vba code for case insensitive comparision

    here you go:

    VB:
    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.

  3. #3
    Join Date
    19th October 2005
    Posts
    18

    Re: vba code for case insensitive comparision

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    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.

  5. #5
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: vba code for case insensitive comparision

    Have a look at the StrComp() function

    Cheers
    Andy


  6. #6
    Join Date
    14th July 2004
    Posts
    10,541

    Re: vba code for case insensitive comparision

    Put Option Compare Text at the top of the code.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th July 2010
    Posts
    8

    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"):
    VB:
     
    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):
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th July 2010
    Posts
    8

    Re: vba code for case insensitive comparision

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

    VB:
    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!!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    14th July 2004
    Posts
    10,541

    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!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Proper Case/Sentence Case In Macro Code
    By Darslg in forum EXCEL HELP
    Replies: 6
    Last Post: May 9th, 2008, 18:18
  2. Time comparision
    By moony01 in forum EXCEL HELP
    Replies: 7
    Last Post: March 13th, 2006, 18:37
  3. Comparision Loop
    By rkayasth in forum EXCEL HELP
    Replies: 5
    Last Post: February 23rd, 2006, 18:18
  4. Stop Case Sensitivity in VBA Code
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: October 4th, 2005, 16:34
  5. Select Case Vba Code
    By macca in forum EXCEL HELP
    Replies: 18
    Last Post: September 20th, 2005, 02:15

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno