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

 

[shg]

It does search all sheets.

 

[simi_uk]

It does search all sheets.

I can see that it is coded to, however if it finds a value that matches on the first sheet, the process exits - i need it to carry on to allow further matches to be displayed?

 

[shg]

It does what your code does (or would do) -- it exits after the first match is found.

 

[simi_uk]

It does what you code does (or would do) -- it exits after the first match is found.

Agreed, my apologies. However is it possible to have the code NOT exit at the point it finds a match and ask the user if they wish to search again for the same term - if no then it exits, if yes it searches again?

 

[shg]

That is built-in functionality if you just use the Find dialog. Why duplicate it?

 

[simi_uk]

That is built-in functionality if you just use the Find dialog. Why duplicate it?

as i said before, my users DO NOT know how to format the search properly - so i need a basic search box that does this for me.

i know it is built in, but my users are not even able to log-on to the internet, so asking them to ormat the search properly to search by Column over the entire Workbook and look for Values as opposed to the default Formula is like asking a dog to meow.