Find Method 256 Character Size Issue

Eldrod

Board Regular
Joined
Mar 11, 2010
Messages
65
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

StevenD

Active Member
Joined
Nov 17, 2004
Messages
262
Try changing

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

to

Code:
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
 
Last edited:

Eldrod

Board Regular
Joined
Mar 11, 2010
Messages
65
No, the .Find still gets an Error 13 when the length of Find_Item is > 256. Any other ideas?
 

StevenD

Active Member
Joined
Nov 17, 2004
Messages
262
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

Board Regular
Joined
Mar 11, 2010
Messages
65

ADVERTISEMENT

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

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,850
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
May be this helps:
Code:
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
 
Last edited:

Eldrod

Board Regular
Joined
Mar 11, 2010
Messages
65

ADVERTISEMENT

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

Board Regular
Joined
Mar 11, 2010
Messages
65
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,850
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
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:
Rich (BB code):

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
 
Last edited:

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,850
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
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 ...
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,227
Messages
5,768,927
Members
425,506
Latest member
Coder33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top