Results 1 to 10 of 10

Type Mismatch from VBA .Find

This is a discussion on Type Mismatch from VBA .Find within the Excel Questions forums, part of the Question Forums category; This is likely Y.A. pathetically useless/misleading/incorrect error message. Anyone know what the real problem might be? XL03 on XP. Run-time ...

  1. #1
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default Type Mismatch from VBA .Find

    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.
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    64,939

    Default

    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?
    If posting code please use code tags.

  3. #3
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    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

  4. #4
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    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

  5. #5
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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.
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  6. #6
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    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

  7. #7
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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!).
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  8. #8
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    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

    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

  9. #9
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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.
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  10. #10
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    You're WELCOME !!!
    I'm still perplexed at the error message.
    I think it's logic

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com