Thursday, December 25, 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.

0 comments:

Post a Comment

Silahkan isikan comment box untuk komentar Anda..