Setting an InputBox to be non-Case Sensitive

[BrianExcel]

Hello -

I am using the following code which (1) Asks the user via InputBox to enter text to search for, (2) Asks the user via Input Box to enter the range to search, and (3) If the data is found, places "Text located" in the cell next to where the record was found.

Sub CommandButton1_Click()Dim r As Range, rAll As Range
Dim sTerm As String
    
    sTerm = Application.InputBox(Prompt:="Enter the text you wish to search for.", Title:="InputBox Method", Type:=2)
    Set rAll = Application.InputBox(Prompt:="Select a Range", Title:="InputBox Method", Type:=8)
 
    If rAll Is Nothing Then
        MsgBox "No Range Selected"
    Else
        For Each r In rAll
            If InStr(r, sTerm) Then
                r.Offset(0, 1) = "Text located"
            End If
        Next r
    End If
End Sub

Here is my issue though. I entered dummy data just to search and see where issues might be, and I noticed that the search and range needed are case sensitive. So for example, I entered "American" as one of the values.

If I search for "American" within the Range, it works great, but if I search "american" (lowercase a instead of upper), it doesn't locate the record.

That said, how do I set my input box to be non-case sensitive so the user can type either way and find what's there?

 

[Norie]

It's not the InputBox you need to make case insensitive, it's the code that checks for the search term.

Try converting both the search term and the text you are searching you the same case, for example.

For Each r In rAll
            If InStr(UCase(r), UCase(sTerm)) Then
                r.Offset(0, 1) = "Text located"
            End If
        Next r

 

[BrianExcel]

That worked PERFECT.

Thanks for your help!