Find Method 256 Character Size Issue

[Eldrod]

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

 

[StevenD]

Try changing

Function FindTextMatch(Find_Item As Variant, Search_Range As Range, Optional LookIn As Variant, Optional LookAt As Variant, Optional MatchCase As Boolean)

to

Function FindTextMatch(Find_Item As String, Search_Range As Range, Optional LookIn As Variant, Optional LookAt As String, Optional MatchCase As Boolean)

Hope this helps

 

[Eldrod]

No, the .Find still gets an Error 13 when the length of Find_Item is > 256. Any other ideas?

 

[StevenD]

Can you tell me what values you are passing to the function as it is called or show me the code that calls the function.

By definition from Excel VBA help "A variable-length string can contain up to approximately 2 billion (2^31) characters."

 

[Eldrod]

Right now, just filling a cell with 257 random characters. Delete one and it passes.

 

[ZVI]

May be this helps:

what:=IIf(Len(Find_Item) > 255, Left(Find_Item, 254) & "*", Find_Item), _

By the way, function should return Long but not Integer.
If Find_Item is the first value in the Search_Range and the same value is somewhere below then your function will never return position 1 of the 1st item.
And seems than FindTextMatch = C.Row is more correct than Duplicate_Text_Ptr = C.Row

 

[Eldrod]

You know that's not the purist solution (which I tend to be), but for my purpose, limiting the text to the first 256 will probably work on 99.999% of my needs. All I'm trying to use this for it to quickly tell me if any duplicate text has already been processed.

I'd prefer it to work in every case, but this may be the best I get. Thanks!

If any other purists can solve this, please do!

 

[Eldrod]

OK, now this is interesting and not in a good way... I just discovered that the variable passing to the .Find referenced as Find_Item gets truncated after the function returns. I confirmed the length as 257 for New_Text_Field (def as Variant) before the function call and its length was 255 (which is what I'm truncating Find_Item to inside the procedure) immediately following the call even though I'm not returning that string.

I guess I can save the string to a temp before the call and copy back afterwards, but that's sloppy. Any of these clues ringing any bells for anyone?

 

[ZVI]

All I'm trying to use this for it to quickly tell me if any duplicate text has already been processed.

How about this simplified:

Function IsDublicate(Find_Item, Search_Range As Range, Optional MatchCase = False) As Boolean
  Dim Arr(), IsUCase As Boolean, i&, s, x
  Arr() = Intersect(Search_Range.Parent.UsedRange, Search_Range).Value
  IsUCase = (VarType(Find_Item) = vbString) And (MatchCase <> False)
  If IsUCase Then
    s = UCase(Find_Item)
  Else
    s = Find_Item
  End If
  For Each x In Arr()
    If IsUCase Then
      i = i - (UCase(x) = s)
    Else
      i = i - (x = s)
    End If
    If i = 2 Then
      IsDublicate = True
      Exit For
    End If
  Next
End Function

 

[ZVI]

variable passing to the .Find referenced as Find_Item gets truncated after the function returns

It is because the arguments by default are passing ByRef, that is - by reference.
In this case all modification of this argument in the function is applied to its instance out of the function.
To avoid this just add ByVal before Find_Item in function arguments declaration like this:
Function FindTextMatch(ByVal Find_Item As Variant ...