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

 

[Eldrod]

Thanks, ZVI! The ByVal addition worked to make the return truncation stop.

Regarding your example, I already had this working as do -loop and needed something faster as the macro processes up to a million cells looking for duplicated text and keeping a list of them. So every time it finds a text cell, it has to scan the list to see if it was already found. If not, then it adds it to the list and resumes. So it get progressively slower the more text it finds.

 

[ZVI]

Thanks, ZVI!
... the macro processes up to a million cells looking for duplicated text and keeping a list of them. So every time it finds a text cell, it has to scan the list to see if it was already found. If not, then it adds it to the list and resumes. So it get progressively slower the more text it finds.

You are welcome!
To make you macro much faster don’t scan the list but better check its existence in the collection or dictionary. Another approach can be in sorting of the data for the first step and the fast loping with comparing of the current value with previous one. There are a lot of examples here for such methods.
If you prefer .Find method for long string then do tranceted findind with additional comparing of full value of the found cell.

To achieve more effective help you could give us more details of your full task, i.e. data before, result after, type of data etc.
Presently I must go away on several hours, but there are gurus around which can help you right now

 

[Eldrod]

I'll have to research using a collection as I have not come across that one yet.

The problem I'm solving is taking large spreadsheet models and extracting every bit of text, formula embedded or standalone values and putting them into a table to accommodate language translations. My macro finds all text, even multiple occurrences in a formula, removes the text, creates unique named range to represent the text it just removed, and puts the named range back into the original cells and formulas in place of the text. It also keeps track of duplicated text and reuses an already-created named range, rather than create another new one. Furthermore, we have a list of custom formatting keywords that it knows to ignore.

So, what I do is write the text and it's named range ID tag to a new sheet, and then rescan the list for a duplicate text and named range before creating a new one. At this point everything works but I'm trying so speed it up as much as I can. A collection may work but, I do need to have the ability to know either what row the duplicate text was on, or identify the named range that was created for that text item.

I'm still a relative newbie to VBA, so any suggestions are appreciated. Thanks!