BrianExcel
Well-known Member
- Joined
- Apr 21, 2010
- Messages
- 975
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.
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?
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.
Code:
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?