Search This Blog

Friday, December 26, 2014

Excel VBA, Prevent Case Sensitive with Ucase function

Ucase Function

 (Menangani case sensitif dengan function Ucase Excel VBA)

The Ucase function can compare text in a range of cells, allowing us to write a macro that compares text case-insensitively.

Here is one that displays a message box if it encounters any cell in A1:A10 of the active sheet containing any case variation of the word CAT.

The code.
To insert the code, press Alt/Option-F11, select Insert → Module, and paste the following:

Sub CompareText( )
Dim rCell As Range

    For Each rCell In Range("A1:A10")
    If UCase(rCell) = "CAT" Then
        MsgBox rCell.Address & " has " & rCell & " in it"
    End If
    Next rCell
End Sub

Exit the VBE and return to Excel and save your workbook.

To run the macro, select Developer → Macros (pre-2007, Tools → Macro → Macros...), select CompareText from the list, and click Run. So if you had the word CAT (uppercase) in cells A1:A9 and the word cat (lowercase) in A10, the macro would display a message box for each instance of the word "cat," regardless of what case it is entered in.

No comments:

Post a Comment