Searching Multiple Sheets

I have come across another problem whilst making my spreadsheet. I have used this forum before where I was able to create the following code from what I learnt. This allows me to search a sheet for a value that I would be able to enter into an input box. I have now run into another problem. I now cannot work out the code that is needed so that it will search more than one sheet. I will need it to search 5 different sheets. I am guessing that it is something to do with a “loop” but I cannot find any information on this to make it work. If anyone could give me the pointers to make this code search multiple sheets I would be very grateful. I am sure that it is something very simple to do, but as I am new to using VB Code it is causing me a few problems. The code that I currently have to search one sheet is shown below. Thank you in advance.


Sub CopyQuote()
Application.ScreenUpdating = False
Dim rngOrder As Range
Dim lngReferenceNumber As Double
lngReferenceNumber = Application.InputBox( _
Prompt:="Enter reference number", _
Title:="Enter reference number", _
Type:=1)
Set rngOrder = Worksheets("Quote History").Range("A1:AI1000").Find( _
what:=lngReferenceNumber, _
lookat:=xlWhole)
If rngOrder Is Nothing Then
MsgBox "Quote not found"
Else
With rngOrder
.Parent.Activate
.Activate
If MsgBox("Do you want to copy this quote?", vbYesNo, "Copying Quote...") = vbYes Then
.EntireRow.Copy
Sheets("Data for Retrieve Quote").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Retrieve Quote").Select
Else
MsgBox "Quote not copied"
Sheets("Main Page ").Select
Application.ScreenUpdating = True
End If
End With
End If
End Sub

Would it do to search the entire workbook, or does it have to be limited to the "5 different sheets" you mention?

Either way would do I suppose, it would be interesting to know both methods...

To loop through all sheets in the book, one can use a simple For Each loop:
Dim thisSheet As Worksheet, rangeMatch As Range
For Each thisSheet In ThisWorkbook.Worksheets
Set rangeMatch = thisSheet.Cells.Find(...)
If Not (rangeMatch Is Nothing) Then Exit For
Next

This sets thisSheet to each worksheet in the workbook's Worksheets collection in turn, tries to find the value, and exits the loop once the find is successful. After the loop, rangeMatch will point to the match or to nothing if there were no matches, just as in your example.

If you want to search a specific subset of sheets, you could build an analogous list to the Worksheets collection yourself. In the standard new 3-page workbook, this would search sheets 1 and 2:
Dim findDomain As Collection: Set findDomain = New Collection
With findDomain
.Add ThisWorkbook.Worksheets("Sheet1")
.Add ThisWorkbook.Worksheets("Sheet2")
End With

For Each thisSheet In findDomain
'etc

Or, you could create a function that searches a given sheet and call that function repeatedly (using the same example as above):
Private Function findInSheet(thisSheet As Worksheet) As Range
Set findInSheet = thisSheet.Cells.Find(...)
End Function
Dim rangeMatch As Range: Set rangeMatch = Nothing

If rangeMatch Is Nothing Then Set rangeMatch = findInSheet(ThisWorkbook.Worksheets("Sheet1"))
If rangeMatch Is Nothing Then Set rangeMatch = findInSheet(ThisWorkbook.Worksheets("Sheet2"))

I'd recommend the loop approach though, even if it's a little more coding effort, as it's more flexible.

Hope that helps! :)

I have tried adapting the above code to what I need it to do and dont seem to be having much luck. Below is the code that I have, I have used it in a blank workbook for testing purposes.


Sub Search_Click()
Application.ScreenUpdating = False
Dim findDomain As Collection: Set findDomain = New Collection
Dim lngReferenceNumber As Double
lngReferenceNumber = Application.InputBox( _
Prompt:="Enter number", _
Title:="Enter number", _
Type:=1)
With findDomain
.Add ThisWorkbook.Worksheets("Sheet1")
.Add ThisWorkbook.Worksheets("Sheet2")
End With

For Each thisSheet In findDomain
Next
If findDomain Is Nothing Then
MsgBox "Quote not found"
Else
With findDomain
If MsgBox("Do you want to copy this quote?", vbYesNo, "Copying Quote...") = vbYes Then
.EntireRow.Copy
Else
MsgBox "Quote not copied"
Application.ScreenUpdating = True
End If
End With
End If
End Sub


When ever I click the button associated with this macro I am able to enter a number but the problem it I allways get the option to copy the result, even though nothing has been found...

The "etc" in my second code sample meant that from that point on, the code should be the same as in the first code sample - as you have it now, the actual .Find method is never used at all.

Sorry about the confusion. :)

I have Been trying for over an hour now and I have still had no luck in making the code working. Could I please have an example of the code...

You already have a complete code sample. :confused:

Is the problem that you're not understanding some of it? If so, you should try looking it up in the VB Help, or ask a more specific question here.
Or do you think you understand all of it but it doesn't work? If so, and you're unable to debug it yourself, you should post your adaptation of the code sample and the details of what doesn't work (error message, where the error occurs, bad result, expected result, etc) here, just as you did above.

Yes, because this method is new to me, the problem is that I dont understand some of it...I cant find how to incorpaorate the two together, as it says that there is a duplicate declaration...

Understanding how the code you're given works is the most important step in getting it to work. As I said, use the Help or ask specific questions about the parts that are new to you.

Well I have this code so far, and I still get the error for a duplicate declaration. I dont know how to correct this error and I think that there may still be something missing...

Sub Search_Click()

Dim findDomain As Collection: Set findDomain = New Collection
With findDomain
.Add ThisWorkbook.Worksheets("Sheet1")
.Add ThisWorkbook.Worksheets("Sheet2")
End With

For Each thisSheet In findDomain
Dim lngReferenceNumber As Double
lngReferenceNumber = Application.InputBox( _
Prompt:="Enter reference number", _
Title:="Enter reference number", _
Type:=1)

Dim thisSheet As Worksheet, rangeMatch As Range
For Each thisSheet In ThisWorkbook.Worksheets
Set rangeMatch = thisSheet.Cells.Find( _
what:=lngReferenceNumber, _
lookat:=xlWhole)

If Not (rangeMatch Is Nothing) Then Exit For
Next
End Sub

You will be able to fix your code once you understand what each line does.

Hope you don't mind Cas, but I'm going to try to help a bit.

Hi HolmesAdam2008,

I think that perhaps we need to slightly revise how you're going to do this. Now, the advice you have been given is perfectly good, but I think (and I say this with the utmost respect) that, given your confusion, working with a user-defined collection is a little beyond your scope at this stage. Please do not take any offense from that - I mean it in an entirely constructive way. Believe it or not, you are actually working with collections already - perhaps without realising it.

Take this slightly modified code that Cas gave you earlier:

Dim wstSheet As Worksheet
For Each wstSheet In ThisWorkbook.Worksheets
msgbox wstSheet.Index
Next wstSheet


This loops through each worksheet in the worksheets collection of thisworkbook and gives you that worksheet's index. The msgbox code, contained within the For Each... Next structure is repeated for each worksheet. In other words, if thisworkbook has 3 worksheets then this structure will loop 3 times so you will get 3 message boxes - one for each worksheet.

Now, if you only want the message box for worksheets called Sheet1 and Sheet2 you can refine it like this:

Sub foo()
Dim wstSheet As Worksheet
For Each wstSheet In ThisWorkbook.Worksheets
If wstSheet.Name = "Sheet1" Or wstSheet.Name = "Sheet2" Then
MsgBox wstSheet.Index
End If
Next wstSheet
End Sub


It still loops through each and every worksheet in the worksheets collection. If thisworkbook has 3 worksheets then it iterates 3 times. On each of these iterations your If statement is assessed. If it returns true then you get the message box; if it returns false then the messagebox is skipped. So it's a little bit inefficient but quite straightforward to understand: here you are introducing conditions which have to be met if the messagebox is to be shown. The conditions will only be met for two of the sheets so you only get two message boxes.

Now notice how I put my Dim statement at the top of the procedure, and not within the For Each... Next loop. Dim statements, by convention, should be placed up at the top of the procedure so they're easy for us programmers to find. We don't like to have to go looking through code to try to find how a variable is declared. It also guarentees that the variable has been declared before you try to use it in your code.

Now some important information: you can't declare two different variables with the same name in the same procedure - it just confuses Excel like crazy.

A small side comment here on your variable naming convention. Normally if we declare a variable with a "lng" prefix that means we are declaring it as Long. So declaring lngReferenceNumber as Double is again a little confusing for us programmers. A prefix such as "dbl" would be more appropriate for a Double type variable.

I really, really, really recommend that you take the time to look at some of our tutorials. They explain declarations, looping, conventions far more thoroughly then we can here.
Standards and Practices (http://www.xtremevbtalk.com/showthread.php?t=68153) is a good starting point. Once you've studied that I recommend you go through a "How to debug" lesson. Here's an example:
http://www.cpearson.com/excel/Debug.htm
There are also many good resources elsewhere on the web and, as Cas said, the Help file is very handy too.

So break down each piece into a simple building block and learn about it. Then, once you understand how to use all of the building blocks, try to put them together, very carefully and one at a time, until you have your house.


I hope that helps,
Colin

As much as I like to buck trends, I have to agree with Colin - declarations really should go at the top of any procedures which are likely to be seen by other programmers, if only because that's where they will expect to find them. Anything else introduces an unnecessary element of confusion, and there are usually plenty enough of those as it is! :D

Hope you don't mind Cas, but I'm going to try to help a bit.
Of course not, I usually learn something new when one of you "old hands" joins a thread. :cool: Your altered approach is definitely conceptually simpler, I never even thought of it.
Take this slightly modified code that Cas gave you earlier:
You forgot to change some of the "thisSheet" references there.

You forgot to change some of the "thisSheet" references there.

Thanks, I've changed it.

I have had a little read and tried a few pieces of code. From what I have learnt and seen I have noticed that the input box appears once for each of sheet which means that I have to enter the number 3 times for a workbook with 3 sheets...Once I have done this it doesnt even select the cell that was being search...I cannot find a solution to the problem...I have spent about a week searching this forum now...:)

Okay, I have a suggestion. Let's try and develop the solution together, step by step. That should not only allow you to solve this problem but also to tackle new ones in the future.

The best approach to something like this is to translate your requirement into pseudo-code (=structured english), then make that pseudo-code more and more detailed until each step can be directly mapped to a VB command.

So, your requirement is (from the OP)
[...] search a sheet for a value that I would be able to enter into an input box. [...] I will need it to search 5 different sheets.
As high-level pseudo-code, it looks like this:

Get value from input box.
Find value in 5 given sheets and assign result to variable rngOrder.

The first refinement could be this:

Declare the variable ReferenceNumber
ReferenceNumber = result of input box
Find ReferenceNumber in 5 given sheets and assign result to variable rngOrder.

Now, your turn again. Keeping it in plain english, try to break this down to steps that VB can manage. We'll help you along if you get stuck.

Hi, again,

I have been having a look around all over the place and I have managed to create this code by using lots of different sources. It does everything I want except one thing which I am hoping someone will be able to help me with. Currently this code searches through all of the sheets for a value that I input using an input box, If it finds the value then it is copied to another sheet, if its not found then a message is displayed. The problem that I am faced with is that the code keeps looping through all of the sheets and pasting the rows until the value that was searched is in the "B1", sometimes when I search for an item it will not be in the first cell of a row. This is where the problem is, I think it is to do with the line:

If ActiveCell.Value = datatoFind Then Exit Sub
Next counter

Is there a way to set this line so that when there is any value in the cell "B1" that the code will Exit Sub. The code that I have created is shown below, thank you in advance... Seems like Cas's way worked with writing down the things i wanted it to do in plain english...:)


Sub Search()

Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Enter Search Criteria")
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:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Selection.EntireRow.Copy
Sheets("Info4Ret").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("No results were found!")
Sheets(currentSheet).Activate
End If
End Sub

This is where the problem is, I think it is to do with the line:

If ActiveCell.Value = datatoFind Then Exit Sub
Next counter

Is there a way to set this line so that when there is any value in the cell "B1" that the code will Exit Sub. The code that I have created is shown below, thank you in advance... Seems like Cas's way worked with writing down the things i wanted it to do in plain english...:)

Hi holmesadam2008,

I'm delighted that you're finding that way is helping you! Cas strikes again! :cool: :)

You can test if a cell is empty with the IsEmpty function. Since you've put a lot of effort in I'll give you an example:

If IsEmpty(ActiveCell) Then
Exit Sub
End If


For now I'd recommend you break it down into three lines like this so you get used to closing your structures off.

Just a little extra info for you....
You'll also see people checking if a cell is empty like this:

If ActiveCell.Value = "" Then
Exit Sub
End If

This way is not as reliable as using the IsEmpty function because the cell you are testing might contain a formula which returns "". That's why I recommend the IsEmpty function.


Let's see if your diagnosis was correct and that fixes your problem?
If not then post back with some more ideas and we'll try to help you out!!

Colin

I really cannot find where I am supposed to place this in the code, if I just replace the line that needs to be taken out then it doesnt work. Now all it does is Search the first sheet and does not proceed to check other sheets.


Sub Search()

Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Enter Search Criteria")
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:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Selection.EntireRow.Copy
Sheets("Info4Ret").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
If IsEmpty(ActiveCell) Then
Exit Sub
End If
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("No results were found!")
Sheets(currentSheet).Activate
End If
End Sub

I seem to be having a problem with it, I have tryed placing the IsEmpty in numorous places and it still hasnt worked. By directly replacing the line it causes the search to only search one sheet and not the whole workbook. Any ideas on where i could place this would be good :):):)


Sub Search()

Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Enter Search Criteria")
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:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Selection.EntireRow.Copy
Sheets("Info4Ret").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
If IsEmpty(ActiveCell) Then
Exit Sub
End If
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("No results were found!")
Sheets(currentSheet).Activate
End If
End Sub


Sorry about the double post it didnt seem that the post had been made, resulting in me posting a new one, sorry if there was any confusion...

Both for your benefit and for our convenience, could I ask you to read post #5 in the snp and format your code accordingly before we go any further? You'll be pleasantly surprised how much easier it becomes to follow what's going on just by adding a few tabs here and there... ;)

Well I read the post that you suggested, Again... and below is the newly formatted code... I think that I have done it correctly....

Sub Search()

Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
'Box to enter the thing to be searched
datatoFind = InputBox("Enter Search Criteria")

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

'The actual Find function
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select

'Once found do this
Selection.EntireRow.Copy
Sheets("Info4Ret").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

'The bit that I cant work out
If IsEmpty(ActiveCell) Then
Exit Sub
End If

Next counter

'If no value is found then do this
If ActiveCell.Value <> datatoFind Then
MsgBox ("No results were found!")

Sheets(currentSheet).Activate

End If
End Sub

No, not really. Maybe the tutorial post wasn't as clear as I remembered it to be...

The point of indenting is that it makes blocks easily recognizable, without having to read each line of code. Blocks are segments of code that are subject to a control structure (I'll give a few examples in a sec to explain what that means). They always have a start-statement and an end-statement; the end-statement is often (but not always) of the form "End xxx" where "xxx" matches the start-statement.
The relevant examples for your code are the following:

Sub ... End Sub

Subs and Functions are VB's most fundamental structures. The code between the start- and end-statements is executed only if the sub or function is "called" from outside - it is "controlled" by the structure, hence the name control structure.


If ... End If

The code inside an If-block is executed only if the condition is fulfilled.


For ... Next

The code inside a For-loop (loop-end-instructions deviate from the "end xxx" pattern) is executed over and over again, controlled by the counter variable.

So, to use some silly pseudo-code as an example:

Sub GoToPenthouse
check current floor
if current floor is not top floor then
for each stair between current floor and top floor
climb stair
next stair
end if
enter penthouse
End Sub

To understand the structure of this procedure, we have to read each line and decide if it's a control structure or an instruction (like "climb stair").

Now, with indenting and a few empty lines, it looks like this:

Sub GoToPenthouse

check current floor

if current floor is not top floor then
for each stair between current floor and top floor

climb stair

next stair
end if

enter penthouse

End Sub

Now, we can just glance at it and know immediately what the basic structure is like: check the floor, then maybe climb some stairs, then enter penthouse.

I hope that made sense, in spite of the silliness of the sample. Have another go at formatting your code. :)

Soooo.....Here's my second attempt :)


Sub Search()

Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index

'Box to enter the thing to be searched
datatoFind = InputBox("Enter Search Criteria")

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

'The actual Find function
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select

'Once found do this
Selection.EntireRow.Copy

Sheets("Info4Ret").Select

Range("B1").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

'The bit that I cant work out
If Not IsEmpty(ActiveCell) Then
Exit Sub
End If

Next counter

'If no value is found then do this
If ActiveCell.Value <> datatoFind Then
MsgBox ("No results were found!")

Sheets(currentSheet).Activate

End If

End Sub

It's still not quite there. You want to ADD one level of indentation whenever you get to a start-statement, and REMOVE one level whenever you get to a stop statement. Otherwise, subsequent lines should always have the SAME level. Like so:
Sub Search() 'start statement

'first level
'first level
'first level

For counter = 1 To sheetCount 'start statement

'second level
'second level
'second level

If Not IsEmpty(ActiveCell) Then 'start statement
'third level
'third level
'third level
End If 'end statement

'second level
'second level
'second level

Next counter 'end statement

'first level
'first level
'first level

If ActiveCell.Value <> datatoFind Then 'start statement
'second level
'second level
'second level
End If 'end statement

'first level
'first level
'first level

End Sub 'end statement

Is this going to be the third time lucky... I have a feeling this is even more wrong than the last...


Sub Search()

Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index

'Box to enter the thing to be searched
datatoFind = InputBox("Enter Search Criteria")

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

'The actual Find function
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select

'Once found do this
Selection.EntireRow.Copy
Sheets("Info4Ret").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

'The bit that I cant work out
If Not IsEmpty(ActiveCell) Then
Exit Sub
End If

Next counter

'If no value is found then do this
If ActiveCell.Value <> datatoFind Then
MsgBox ("No results were found!")

Sheets(currentSheet).Activate

End If

End Sub

Apparently, I still haven't explained the difference between control structures on the one hand and instructions on the other well enough. Have a look at MSDN's Introduction to Control Structures (http://msdn.microsoft.com/en-us/library/aa242123(VS.60).aspx) section. Apart from hopefully clarifying the concepts, the three pages contain plenty of examples showing standard indentation.

You could even use them as practice pieces - copy some example code, remove the indentation, then try to re-create it and compare with the original.

Sorry to insist on this, but it really is that important, not in and of itself but because being able to indent code and being able to understand code are so very closely linked.

Really, I have had three tries at this now and I don't seem to be getting anywhere, would it not make more sense for you to format the code for me to enable me to advance and learn from what I see instead of filling this forum with lots of peices of incorrectly formatted code....Indentation only affects the presentation and since it is only me that is ever going to read this code then it doesnt really matter that much...I'm still looking for the answer as to where to place the IsEmpty Statement... Thanks...

The only explanation for your incorrectly formatted code that I can think of is a flaw or lack in your understanding of the concepts of control structures vs. instructions. These concepts are so fundamental that I can't begin to formulate any code logic without them.

So, don't take this the wrong way, but I'm unwilling to proceed as you suggest.

Is there anyone else that would like to help me on this topic....

Another quick question....With the search that I have it only allows me to search for a TEXT value or anything in a cell with the formatting of "general"... I need to be able to search for any value i.e. number, currency and also text. Is there a way to Dim datatoFind as all of those... In the help file it states that if something is not defined then it is preset as Variant...This still does not allow for me to search for numbers and currency etc.... Cheers

It should be fine the way you have it.
Dim v
Dim v As Variant

Both declare v as a variant. Variants can hold data of any format, including any numeric type and text.

Yes it seems that you are able to enter any type into the input box, BUT.... it will not find anything other than a text value in the cells...or more precisely anything in a cell which has the formatting set to "General"....