Type Mismatch from VBA .Find

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
This is likely Y.A. pathetically useless/misleading/incorrect error message. Anyone know what the real problem might be? XL03 on XP.
Run-time error '13':
Type Mismatch
Code:
dim c as Range
...
Set c = .Find(what:="LIQ", _
                      LookIn:=xlFormulas, _
                      LookAt:=xlPart, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlNext, _
                      After:=ActiveCell, _
                      MatchCase:=False)
ActiveCell is a single cell on an unhidden sheet - cell A279.

I can Control-F at this point in Excel, and it finds "LIQ" just fine.

Now here comes the queer part. At least at THIS moment, if activecell is on rows 1 through 26, NO PROBLEM. If it's on rows 28+, death message shown above. J27 is the point where death begins. I've cleared i27:k27 and there's nothing notable there. L279 is the end of UsedRange.

Since this clearly is a bug, I'll really reach far now: there is a "drawing symbol" (a big "right brace") that begins at J34, if that's a clue. (I'm mystified as to what worksheet cells are connected to big drawing symbols like this.)

If anyone recognizes this and can "succint-ize" it down to a simple reproducible example, that would be useful too, if we need to go to Usenet on this.

Thanks.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
GIA

A few points.

1 Is whatever you are looking for actually there?

2 Are you sure you want to search formulas?

3 Are you sure that ActiveCell is actually referring to the cell you want?

4 How are you setting the ActiveCell?
 
Upvote 0
Hi,

when the "after" or "before" cell is not within the range where the find is done you will get a mismatch

example which will bug
Code:
Set c = Range("A1:B5").Find(what:="this", after:=Range("A6"))
A6 is not in the range A1:B5

using an error handler
Code:
Sub test()
Dim c As Range

    With Range("A1:B5")
        If Intersect(.Offset(0, 0), ActiveCell) Is Nothing Then
        MsgBox "impossible to perform ''find'' because the activecell is not within the range " & .Address, 48, "ERROR"
        Exit Sub
        End If
    Set c = .Find(what:="this", after:=ActiveCell)
    End With
End Sub

it's like you would explain a route
turn right after the first lights in this street
while there are no lights in the entire street


kind regards,
Erik
 
Upvote 0
GIA

A few points.

1 Is whatever you are looking for actually there?

2 Are you sure you want to search formulas?

3 Are you sure that ActiveCell is actually referring to the cell you want?

4 How are you setting the ActiveCell?
Norie,
can you show how each of those would trigger "Type Mismatch" ??
I presume you wouldn't ask those questions if you didn't know that they can generate the mismatch-error
 
Upvote 0
Any further knowledge about "After" quirks would be good to hear now, all.

Great idea, Eric. I poorly neglected to mention the prior statement
With wksht.UsedRange
and wksht is dimmed as worksheet and equals Worksheets(2). I suck for omitting that useful info.

Nonetheless, the problem is still something else. Every cell I've mentioned above is within the UsedRange A1:L279. Besides, I don't think being outside the range is a problem. I tested your IF block with the active cell set way off down and to the right of everything... and it tripped an error, but the code actually had no problem searching the specified range.

I do think you've zeroed in on the problem. I don't think I've ever had any problems with
After:=ActiveCell.SpecialCells(xlLastCell)

"After" really does seem to be the villain.

Norie, thanks, but none of those items pan out.
 
Upvote 0
you might consider to send me your sheet: I would be glad to help you taking a look (first going to sleep)

may I insist: did you insert this ?
Code:
        If Intersect(.Offset(0, 0), ActiveCell) Is Nothing Then 
        MsgBox "impossible to perform ''find'' because the activecell is not within the range " & .Address, 48, "ERROR" 
        Exit Sub 
        End If
a quick way to know if you didn't miss the obvious
 
Upvote 0
I pasted it from your message.

Crap, now I'm getting failure from
After:=ActiveCell.SpecialCells(xlLastCell)

Thank you for your kind offer, but let me try to pare the size down for that. There are many sheets, etc., and the code is among the thousands and thousands of lines of my personal.xls modules (heck, I even have more than one file for personal macros - not counting Walkenbach's PUP either!).
 
Upvote 0
Hi,

your workbook (small version) arrived
did you really insert my little check ? the error is "intersect not possible"
why ?? ... let's take a look at your code
Code:
      With wksht.UsedRange
        For i = 1 To iArgs 'Loop through search args; if match beyond last reported, lngBestYet will be set
            If bDontCheck(i) Then GoTo ContinueNext 'I'll GoTo rather than indent more!Where the hell is continue in VB?
            Set rngAfterCell = IIf(lngLastReported = 0, ActiveCell.SpecialCells(xlLastCell), ActiveCell)
      MsgBox rngAfterCell.Parent.Name & " " & .Address, 64, wksht.Name
            Set c = .Find(what:=sSearchArray(i), _
                          LookIn:=lLookIn, _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          After:=rngAfterCell, _
                          MatchCase:=False)       'Note: because After...LastCell, .Find finds FIRST match on the sheet!
I inserted one line
Code:
      MsgBox rngAfterCell.Parent.Name & " " & .Address, 64, wksht.Name
and think you are smart enough to get it now :biggrin:

the activecell is on the current activewindow and not on you wksht
so indeed the activecell was not in the range, hence the mismatch error

you will need to select the sheets one by one if you want to apply "activecell"

best regards,
Erik
 
Upvote 0
Excellent work! In fact I had cutely optimized some "For Each wksht In Worksheets" to avoid selecting (activating). I won't do that anymore :)

As to the activecell being out of the search range, I'm having a hard time invoking that error. Excel just keeps smiling at me. However, in the past, I came to the same conclusion as you, because I just found I had code that handles this (which for some reason I deleted! I'll revive it now :)
Code:
If Selection.Cells.Count > 1 Then
        Set rngLookBreadth = Selection
    Else
        Set rngLookBreadth = Union(ActiveCell, ActiveSheet.UsedRange)
    End If
    Set c = rngLookBreadth.Find ...
I'm still perplexed at the error message.

By the way, the other day I couldn't get Intersect to fail (never got the error message). Today, I am getting a run time error with the intersect. It seems that On Error is appropriate for it.
 
Upvote 0
You're WELCOME !!!
I'm still perplexed at the error message.
I think it's logic :biggrin:

Code:
Sub type_mismatch1()
'variant can be "error"
'double can not
Dim i As Variant
i = Application.Match(1, Array(4, 5, 8), 0)
Debug.Print i

Dim j As Double
j = Application.Match(1, Array(4, 5, 8), 0)
End Sub

Sub type_mismatch2()
'range can be "nothing" but not "error"
Dim c As Range
Set c = Range("A1:B5").Find(what:="this", after:=ActiveCell)
End Sub
when trying to set the range you can not assign "error" as range, hence "type mismatch"

greetings form Belgium,
Erik
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top