I hope someone can help with this as I've searched a while and can't find anything on this. I have the following function I found somewhere else to use the .Find method to locate an item in a range. My usage is to use this to retrieve the row number of the match (if found) rather than a much slower DO Loop. This code works until I come across a cell with a value length greater than 256 characters. The macro then fails with an error code 13 - Type mismatch. Anyone know how to get around this? Thanks!
Function FindTextMatch(Find_Item As Variant, Search_Range As Range, Optional LookIn As Variant, Optional LookAt As Variant, Optional MatchCase As Boolean) As Integer
Dim C As Range
If IsMissing(LookIn) Then LookIn = xlValues ' xlFormulas, xlComments
If IsMissing(LookAt) Then LookAt = xlWhole ' xlPart
If IsMissing(MatchCase) Then MatchCase = False
With Search_Range
Set C = .Find( _
what:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False)
If Not C Is Nothing Then
'Set Find_Range = C
Duplicate_Text_Ptr = C.Row
Else
Duplicate_Text_Ptr = 0
End If
End With
End Function
Function FindTextMatch(Find_Item As Variant, Search_Range As Range, Optional LookIn As Variant, Optional LookAt As Variant, Optional MatchCase As Boolean) As Integer
Dim C As Range
If IsMissing(LookIn) Then LookIn = xlValues ' xlFormulas, xlComments
If IsMissing(LookAt) Then LookAt = xlWhole ' xlPart
If IsMissing(MatchCase) Then MatchCase = False
With Search_Range
Set C = .Find( _
what:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False)
If Not C Is Nothing Then
'Set Find_Range = C
Duplicate_Text_Ptr = C.Row
Else
Duplicate_Text_Ptr = 0
End If
End With
End Function