#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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. |
#3
|
||||
|
||||
@ 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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
||||
|
||||
And, BTW, don't be afraid to use some common courtesy. "Hello" and Thank you" are small things always appreciated
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
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. |
#6
|
||||
|
||||
|
#7
|
||||
|
||||
And there is a lot of useful HowTo's in the FAQ
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#8
|
|||
|
|||
Thanks! I will review the FAQ section
|
Tags |
type mismatch |
|
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 |