Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007

    Lightbulb Unanswered: Could someone enlighten me about "Option Compare Text"?

    I have this formatting macro that well deletes stuff, highlights a row based on certain arguments, etc. I know Option Compare Text makes a module case insensitive, I was wondering if this ability extends to the arguments for Nested Ifs & Cases w/in that module. Since I need to make the arguments case insensitive. Ex. If "Tax" = "TAX" = "tax", delete the entire row...

  2. #2
    Join Date
    Mar 2006
    Well I think the short answer is yes, that's the whole point of Option Compare Text.

  3. #3
    Join Date
    Feb 2004
    I have never used Option Compare Text for this purpose or any purpose. Using the text functions "uCase" or "lCase" are good way to avoid case problems when comparing text. Here's an example.

    Public Sub checkRows()
    nRow = 25
    For i = nRow To 2 Step -1
        bMatch = (UCase(Cells(i, 1)) = UCase(Cells(i, 2)))
        If bMatch Then
        End If
    End Sub
    Notice in the example I used 'Step -1' argument of the 'For' Loop to work backwards through the range. This way when a row is deleted it is not necessary to re-define the range because the subsequent row numbers have changed when the row has been deleted.

    Normally I would use a function or statement to assign the last row number of the used range to the variable 'nRow' however for this simple example I just assigned the variable a number.

    It doesn't make a difference if LCase or UCase funtion is used. The purpose is to ensure values are compared without a difference in case.


  4. #4
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2
    Hi Bill

    I tend to do exactly the same. The only time I've used Option Compare is when verifying passwords that are case sensitive.


Posting Permissions

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