how to make VBA search case INsensitive

[simi_uk]

hi guys,

here's my current code:

Private Sub Search_Click()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
On Error Resume Next
currentSheet = ActiveWorkbook.Index
datatoFind = InputBox()
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(what:=datatoFind, after:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("The search returned no hits.")
Sheets(currentSheet).Activate
End If
End Sub

i need to make the above search function case INsensitive, that is if i want to search for 'AbcdeF' it will find 'abcdef' and 'AbCdEf' as well as 'ABCDEF' or any iteration of these...

Also, is it possible to add a wildcard function to the above so that i could search 'Abc' or even 'CdE' and it find all the above examples - if so how?

many thanks in advance for any help

 

[Domenic]

For a case-insensitive search, you've already set MatchCase to False, so that's fine. For a partial search, replace...

LookAt:=xlWhole

with

LookAt:=xlPart

 

[simi_uk]

Hi domenic,

with the code appearing as

:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate

the case sensitivity still applies....

i amended the code to remove any line breaks and also to inlcude the LookAt:xlPart in place of LookAt:=xlWhole, but i'm still having a problem with it regards partial match searches. For example, say there is a value in the worksheet of 123-abc -def and I were to search for the term abc-def, the search returns saying it has no matches.

I should also note that there is the possiblity that multiple instances of a matching value (e.g 123abc, 123abc-def) are present on various worksheets, so when an entry is found, the search should prompt to continue to find further matches.

here's the code to make sure i'm doing it correctly:

Option Explicit
Option Compare Text
Private Sub Search_Click()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
On Error Resume Next
currentSheet = ActiveWorkbook.Index
datatoFind = InputBox()
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(what:=datatoFind, after:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:=False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("The search returned no hits.")
Sheets(currentSheet).Activate
End If
End Sub

 

[pcc]

putting

Option Compare Text

at the top of the module that contains the code should make it case-insensitive

 

[simi_uk]

putting

Option Compare Text

at the top of the module that contains the code should make it case-insensitive

Hi,

thanks for the reply, but thi is already in my code above....

 

[shg]

For example, say there is a value in the worksheet of 123-abc -def and I were to search for the term abc-def, the search returns saying it has no matches.

The string in the cell has a space before the second hyphen.

You might search for abc*def

 

[shg]

If I may ask, what does your code do that you couldn't do from the Find dialog, with the option selected to search the entire workbook?

 

[simi_uk]

If I may ask, what does your code do that you couldn't do from the Find dialog, with the option selected to search the entire workbook?

Thanks for the reply, i've tried using wildcards but i get no matches when doing so.

I'm trying NOT to have to use the Find command as the users of the workbook are not at all experienced (read: total computer novices), so having them click a 'search' button and that button being made to run my VBA code is a far simpler way of providing the workbook search for them.

 

[shg]

Irrespective, here's another way to code it:

Private Sub Search_Click()
    Dim vWhat       As Variant
    Dim wks         As Worksheet
    Dim rFind       As Range
 
    vWhat = InputBox(Prompt:="Find what?")
    If Len(vWhat) = 0 Then Exit Sub
 
    For Each wks In Worksheets
        Set rFind = wks.Cells.Find(What:=vWhat, _
                                   LookIn:=xlValues, _
                                   LookAt:=xlPart, _
                                   MatchCase:=False, _
                                   SearchFormat:=False)
        If Not rFind Is Nothing Then
            Application.Goto rFind
            Exit Sub
        End If
    Next wks
 
    MsgBox ("Not found")
End Sub

 

[simi_uk]

Irrespective, here's another way to code it:

Private Sub Search_Click()
    Dim vWhat       As Variant
    Dim wks         As Worksheet
    Dim rFind       As Range
 
    vWhat = InputBox(Prompt:="Find what?")
    If Len(vWhat) = 0 Then Exit Sub
 
    For Each wks In Worksheets
        Set rFind = wks.Cells.Find(What:=vWhat, _
                                   LookIn:=xlValues, _
                                   LookAt:=xlPart, _
                                   MatchCase:=False, _
                                   SearchFormat:=False)
        If Not rFind Is Nothing Then
            Application.Goto rFind
            Exit Sub
        End If
    Next wks
 
    MsgBox ("Not found")
End Sub

This works, but only for the first sheet, is there anyway to have it search ALL sheets?