Eliminate Case-Sensitivity in Search

[Marty]

Hello, all:

I'm using Excel 2003. I've written a short piece of code to do a search of
a column of cells containing string variables. I'd like to know how I can
get past the problem of case-sensitivity.

The specific problem is that if "MARTY" appears in the cells that I'm
performing my search on, it won't return a hit on "marty", "Marty", "MaRtY",
etc., but I want it to. Here is the code (activated by a command button):
******************************************
Private Sub CommandButton1_Click()
Dim SEARCHTXT, MASTERTXT As String
Dim DISPLAY, MASTERLIST As Object
Set DISPLAY = Workbooks(ActiveWorkbook.Name).Sheets("Sheet3")
Set MASTERLIST = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
SEARCHTXT = DISPLAY.Cells(1, 2) 'The cell in which the search test is entered.
SEARCHTXTLENGTH = Len(SEARCHTXT)
RESULTROW = 8 'the first row in which the search results are displayed
For MASTERROW = 4 To MASTERLIST.Range("A2") + 3 'going down the column
looking for the search text
MASTERTXT = MASTERLIST.Cells(MASTERROW, 1) 'looking at specific cells
(there are thousands of them)
If Left(MASTERTXT, SEARCHTXTLENGTH) = SEARCHTXT Then 'match found
DISPLAY.Cells(RESULTROW, 1) = MASTERTXT
RESULTROW = RESULTROW + 1 'increment the display row by 1
If RESULTROW = 18 Then
Dim Msg, Style, Title
Msg = "Search returned more than 10 results." & Chr(13) &
Chr(13) & "If your desired result is not shown," & Chr(13) & "use a more
specific search string."
Style = vbOKOnly + vbExclamation
Title = "Search Limit Exceeded"
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then
GoTo 100:
End If
End If
End If
Next MASTERROW
If RESULTROW = 8 Then
Dim Msg2, Style2, Title2
Msg2 = "No results were found which match your search query."
Style2 = vbOKOnly + vbExclamation
Title2 = "No Results"
Response = MsgBox(Msg2, Style2, Title2)
If Response = vbOK Then
GoTo 100:
End If
End If
100:
End Sub
*************************************
I would imagine I need to modify this statement:

If Left(MASTERTXT, SEARCHTXTLENGTH) = SEARCHTXT Then

Any help would be greatly appreciated.

Thanks in advance,
MARTY

 

[Jim Rech]

Just add Option Compare Text at the top of the module.

--
Jim Rech
Excel MVP
| Hello, all:
|
| I'm using Excel 2003. I've written a short piece of code to do a search
of
| a column of cells containing string variables. I'd like to know how I can
| get past the problem of case-sensitivity.
|
| The specific problem is that if "MARTY" appears in the cells that I'm
| performing my search on, it won't return a hit on "marty", "Marty",
"MaRtY",
| etc., but I want it to. Here is the code (activated by a command button):
| ******************************************
| Private Sub CommandButton1_Click()
| Dim SEARCHTXT, MASTERTXT As String
| Dim DISPLAY, MASTERLIST As Object
| Set DISPLAY = Workbooks(ActiveWorkbook.Name).Sheets("Sheet3")
| Set MASTERLIST = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
| SEARCHTXT = DISPLAY.Cells(1, 2) 'The cell in which the search test is
entered.
| SEARCHTXTLENGTH = Len(SEARCHTXT)
| RESULTROW = 8 'the first row in which the search results are displayed
| For MASTERROW = 4 To MASTERLIST.Range("A2") + 3 'going down the column
| looking for the search text
| MASTERTXT = MASTERLIST.Cells(MASTERROW, 1) 'looking at specific cells
| (there are thousands of them)
| If Left(MASTERTXT, SEARCHTXTLENGTH) = SEARCHTXT Then 'match found
| DISPLAY.Cells(RESULTROW, 1) = MASTERTXT
| RESULTROW = RESULTROW + 1 'increment the display row by 1
| If RESULTROW = 18 Then
| Dim Msg, Style, Title
| Msg = "Search returned more than 10 results." & Chr(13) &
| Chr(13) & "If your desired result is not shown," & Chr(13) & "use a more
| specific search string."
| Style = vbOKOnly + vbExclamation
| Title = "Search Limit Exceeded"
| Response = MsgBox(Msg, Style, Title)
| If Response = vbOK Then
| GoTo 100:
| End If
| End If
| End If
| Next MASTERROW
| If RESULTROW = 8 Then
| Dim Msg2, Style2, Title2
| Msg2 = "No results were found which match your search query."
| Style2 = vbOKOnly + vbExclamation
| Title2 = "No Results"
| Response = MsgBox(Msg2, Style2, Title2)
| If Response = vbOK Then
| GoTo 100:
| End If
| End If
| 100:
| End Sub
| *************************************
| I would imagine I need to modify this statement:
|
| If Left(MASTERTXT, SEARCHTXTLENGTH) = SEARCHTXT Then
|
| Any help would be greatly appreciated.
|
| Thanks in advance,
| MARTY

 

[Harald Staff]

Hi Marty

Use Excel's own Find instead. Far faster, and it takes parametres like
MatchCase. Record a macro while doing it manually for code, edit down to
something like this for a start

Cells.Find(What:="marty", LookIn:=xlValues, LookAt:= _
xlPart, MatchCase:=False).Activate

HTH. Best wishes Harald

 

[Tom Ogilvy]

You also might want to look at the help example in the VBE for the FindNext
method.

 

[Marty]

Jim, Harald & Tom:

Everything that was suggested worked. Thanks very much, gentlemen. I
appreciate the help.

MARTY