Search String not ignoring CAPS

[tony.reynolds]

how can i make the following code return results for words in UPPERCASE the same as lowercase

At the moment if the string includes the word Test it finds "Test" but not "test" for instance or
it finds "Test" not "TEST"

Private Sub TextBoxSearchItemList_Change()
Application.ScreenUpdating = False
Range("FilteredPartsList").ClearContents
Dim FilteredListTopLeftCorner As Range
Dim FilteredListBottomLeftCorner As Range
Dim CurrentSearchCell As Range
Dim CurrentFilteredListCell As Range
Dim Check1 As Boolean
Dim SearchString1
Dim SearchString2
If TextBoxSearchItemList = Empty Or TextBoxSearchItemList Like "?" Or _
TextBoxSearchItemList Like "??" Or _
TextBoxSearchItemList Like "???" Or _
TextBoxSearchItemList Like "????" _
Then
NewPartDescriptionChart.RowSource = "AllPartsViewColumns"
Exit Sub
End If
Set FilteredListTopLeftCorner = Range("FilteredItemsStart")
'Filter available Parts
Set CurrentSearchCell = Range("PartDescriptions").Offset(1, 0)
Set CurrentFilteredListCell = Range("FilteredItemsStart")
SearchString1 = CurrentSearchCell.Text & " " & CurrentSearchCell.Offset(1, 0).Text
SearchString2 = "*" & TextBoxSearchItemList & "*"
Do
SearchString1 = CurrentSearchCell.Text & " " & CurrentSearchCell.Offset(0, 1).Text
Check1 = SearchString1 Like SearchString2
If Check1 = True Then
CurrentFilteredListCell = CurrentSearchCell
CurrentFilteredListCell.Offset(0, 1) = CurrentSearchCell.Offset(0, 1)
CurrentFilteredListCell.Offset(0, 2) = Format(CurrentSearchCell.Offset(0, 2), "$#,##0.00")
CurrentFilteredListCell.Offset(0, 3) = CurrentSearchCell.Offset(0, 3)
CurrentFilteredListCell.Offset(0, 4) = CurrentSearchCell.Offset(0, 4)
CurrentFilteredListCell.Offset(0, 5) = CurrentSearchCell.Offset(0, 5)
CurrentFilteredListCell.Offset(0, 6) = CurrentSearchCell.Offset(0, 6)
Set CurrentFilteredListCell = CurrentFilteredListCell.Offset(1, 0)
Set CurrentSearchCell = CurrentSearchCell.Offset(1, 0)
Else
Set CurrentSearchCell = CurrentSearchCell.Offset(1, 0)
End If
Loop Until CurrentSearchCell = Empty
Set FilteredListBottomLeftCorner = CurrentFilteredListCell.Offset(0, 6)
ActiveWorkbook.Names.Add Name:="FilteredPartsList", RefersTo:=Range(FilteredListTopLeftCorner, FilteredListBottomLeftCorner)
NewPartDescriptionChart.RowSource = "FilteredPartsList"

End Sub

Any ideas would be great

 

[Tom Urtis]

Based on this you wrote:

At the moment if the string includes the word Test it finds "Test" but not "test" for instance or
it finds "Test" not "TEST"

It sounds like, contrary to your subject line "not ignoring CAPS" that you *do* want to ignore CAPS and accept any upper or lower case combination.

One way to accomplish this is to place the following statement at the top of your module:

Option Compare Text

Note, "at the top of your module" means just that -- above and outside of all macros UDFs, and procedures.

 

[tony.reynolds]

Actually i worked it out.

i chanced the strings to this...

SearchString1 = StrConv(CurrentSearchCell & " " & CurrentSearchCell.Offset(1, 0).Text, vbLowerCase)
SearchString2 = StrConv("*" & TextBoxSearchItemList & "*", vbLowerCase)

Fixes it.

Thanks anyway ill try your suggestion too.

 

[tony.reynolds]

One way to accomplish this is to place the following statement at the top of your module:

Option Compare Text
QUOTE]

Yes thats much better.

I found that the way i tried to do it with

StrConv("*" & TextBoxSearchItemList & "*", vbLowerCase)

dosnt work if the string starts with a number eg "12 Volt Power" will not convert to "12 volt power"

Thanks a stack tom