Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-03-2019, 01:37 PM
abenitez77 abenitez77 is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 8
abenitez77 is on a distinguished road
Exclamation Type mismatch error

I ran into an error when searching values and
It errors out on this line:

If (FoundCell.Address = FirstFound.Address) Then

Error is error number 13 - Type mismatch

FoundCell.Address is "$A$6" and FirstFound.Address is "$A$5"

The value in $A$5 is "Vendor # Vienna Beef"
The value in $A$6 is "Vendor # 270645-97-0"




These are the values passed to the function below (FindAll)
MySearch = "Vendor #"
xlValues = -4163
xlSearchType = 2
xlByColumns = 2
MatchCase = False

Set ResultRange = FindAll(SearchRange, MySearch, xlValues, xlSearchType, xlByColumns, MatchCase)

Code:
Code:
Function FindAll(SearchRange As Range, _
                FindWhat As Variant, _
               Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False, _
                Optional BeginsWith As String = vbNullString, _
                Optional EndsWith As String = vbNullString, _
                Optional BeginEndCompare As VbCompareMethod = vbTextCompare) As Range
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' FindAll
    ' This searches the range specified by SearchRange and returns a Range object
    ' that contains all the cells in which FindWhat was found. The search parameters to
    ' this function have the same meaning and effect as they do with the
    ' Range.Find method. If the value was not found, the function return Nothing. If
    ' BeginsWith is not an empty string, only those cells that begin with BeginWith
    ' are included in the result. If EndsWith is not an empty string, only those cells
    ' that end with EndsWith are included in the result. Note that if a cell contains
    ' a single word that matches either BeginsWith or EndsWith, it is included in the
    ' result.  If BeginsWith or EndsWith is not an empty string, the LookAt parameter
    ' is automatically changed to xlPart. The tests for BeginsWith and EndsWith may be
    ' case-sensitive by setting BeginEndCompare to vbBinaryCompare. For case-insensitive
    ' comparisons, set BeginEndCompare to vbTextCompare. If this parameter is omitted,
    ' it defaults to vbTextCompare. The comparisons for BeginsWith and EndsWith are
    ' in an OR relationship. That is, if both BeginsWith and EndsWith are provided,
    ' a match if found if the text begins with BeginsWith OR the text ends with EndsWith.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Dim FoundCell As Range
    Dim FirstFound As Range
    Dim LastCell As Range
    Dim ResultRange As Range
    Dim XLookAt As XlLookAt
    Dim Include As Boolean
    Dim CompMode As VbCompareMethod
    Dim Area As Range
    Dim MaxRow As Long
    Dim MaxCol As Long
    Dim BeginB As Boolean
    Dim EndB As Boolean
    
    
    CompMode = BeginEndCompare
    If BeginsWith <> vbNullString Or EndsWith <> vbNullString Then
        XLookAt = xlPart
    Else
        XLookAt = LookAt
    End If
    
    ' this loop in Areas is to find the last cell
    ' of all the areas. That is, the cell whose row
    ' and column are greater than or equal to any cell
    ' in any Area.
    
    For Each Area In SearchRange.Areas
        With Area
            If .Cells(.Cells.Count).Row > MaxRow Then
                MaxRow = .Cells(.Cells.Count).Row
            End If
            If .Cells(.Cells.Count).Column > MaxCol Then
                MaxCol = .Cells(.Cells.Count).Column
            End If
        End With
    Next Area
    Set LastCell = SearchRange.Worksheet.Cells(MaxRow, MaxCol)
    
    On Error GoTo 0
    Set FoundCell = SearchRange.Find(What:=FindWhat, _
            after:=LastCell, _
            LookIn:=LookIn, _
            LookAt:=XLookAt, _
            SearchOrder:=SearchOrder, _
            MatchCase:=MatchCase)
    
    If Not FoundCell Is Nothing Then
        Set FirstFound = FoundCell
        Do Until False ' Loop forever. We'll "Exit Do" when necessary.
            Include = False
            If BeginsWith = vbNullString And EndsWith = vbNullString Then
                Include = True
            Else
                If BeginsWith <> vbNullString Then
                    If StrComp(Left(FoundCell.Text, Len(BeginsWith)), BeginsWith, BeginEndCompare) = 0 Then
                        Include = True
                    End If
                End If
                If EndsWith <> vbNullString Then
                    If StrComp(Right(FoundCell.Text, Len(EndsWith)), EndsWith, BeginEndCompare) = 0 Then
                        Include = True
                    End If
                End If
            End If
            If Include = True Then
                If ResultRange Is Nothing Then
                    Set ResultRange = FoundCell
                Else
                    Set ResultRange = Application.Union(ResultRange, FoundCell)
                End If
            End If
            Set FoundCell = SearchRange.FindNext(after:=FoundCell)
            If (FoundCell Is Nothing) Then
                Exit Do
            End If
            If (FoundCell.Address = FirstFound.Address) Then
                Exit Do
            End If
    
        Loop
    End If
        
    Set FindAll = ResultRange

End Function

Last edited by Pecoflyer; 04-03-2019 at 11:22 PM. Reason: Added code tags
Reply With Quote
  #2  
Old 04-03-2019, 04:01 PM
p45cal p45cal is offline Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 253
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

I can't reproduce your error.
Attaching a workbook where this happens would be very useful to us.

While trying to make this go wrong, I did manage to get a type mismatch error, but on the line:
Set FoundCell = SearchRange.Find(What:=FindWhat,
in the circumstances that I passed a non-contiguous range such as:
Range("$A$1:$A$3,$A$5:$A$7,$A$9:$A$10,$C$1:$C$2")
where the line:
Set LastCell =
immediately above sets LastCell to be a cell which doesn't exist in any area in the non-contiguous range to be searched. In this picture, the yellow cell is what LastCell is set to, and the grey area is the area to be searched, so in the .Find statement, After:=LastCell is not a cell in any area.




So really, if you can attach a file, with the code that makes it throw an error, it would be best. I don't even know whether you're searching non-contiguous ranges or not.

I also seem to remember from some time back that .FindNext doesn't work very well with non-contiguous ranges and I found I had to use .Find all the time with the After:= parameter set each time.
Reply With Quote
  #3  
Old 04-03-2019, 11:21 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,386
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

@ Abenitz
Hi and welcome
In the future pleas wrap your code with code tags ( select code and click the # button)
I did it for you this time
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #4  
Old 04-03-2019, 11:23 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,386
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

And, BTW, don't be afraid to use some common courtesy. "Hello" and Thank you" are small things always appreciated
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #5  
Old 04-04-2019, 06:59 AM
abenitez77 abenitez77 is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 8
abenitez77 is on a distinguished road
Default

Thanks all. PecoFlyer, I'm not afraid of common courtesy : ) ...you didn't give me a chance to reply to P45Cal and thank him for his response. But, thank You for the reminder. Also thank you for wrapping my code, I will keep that in mind next time.

How do I attach a file? I would like to share it with P45Cal and others so they can look at the example I am having issues with.
Reply With Quote
  #6  
Old 04-04-2019, 07:05 AM
p45cal p45cal is offline Windows 10 Office 2016
Expert
 
Join Date: Apr 2014
Posts: 253
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

Quote:
Originally Posted by abenitez77 View Post
How do I attach a file?
http://www.msofficeforums.com/faq.ph...b3_attachments
Reply With Quote
  #7  
Old 04-04-2019, 07:48 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,386
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

And there is a lot of useful HowTo's in the FAQ
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #8  
Old 04-04-2019, 08:51 AM
abenitez77 abenitez77 is offline Windows 10 Office 2016
Novice
 
Join Date: Apr 2019
Posts: 8
abenitez77 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
And there is a lot of useful HowTo's in the FAQ
Thanks! I will review the FAQ section
Reply With Quote
Reply

Tags
type mismatch

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Type Mismatch when RibbonX applying crtx to Chart? Elinor Excel Programming 0 06-03-2016 06:45 AM
Name mismatch b/n two columns hiruy Excel 1 12-18-2015 07:14 AM
VBA in Excel: Add a new Word Document on Template (Type Mismatch Error)) tinfanide Excel Programming 1 03-29-2012 09:10 AM
[Mismatch] Help needed!!!!!!!! rockfielder Word 3 08-25-2011 11:45 AM
Resolution mismatch Adforum PowerPoint 5 05-25-2011 12:24 PM


All times are GMT -7. The time now is 10:24 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft