Solved: Range.Find question - Max length "What:=" string can be

[malik641]

Having a problem with using the Range.Find method. I have other ways of doing what I want to do, but it made me curious to know if there is a limit to the "What" parameter of the Range.Find method.

Here was the code that failed because of a "Type Mismatch" (which makes me think that it's not the amount of text in the what.....but I can't think of anything else):

[vba]Set cell = ContractorWS.Range("A1", ContractorWS.Cells.SpecialCells(xlCellTypeLastCell).Address) _
.Find(What:=txtPackage.Text, LookIn:=xlValues)[/vba]
And here's the text:
Up to 84 L.F. (15' x 30'), 3' to 5' depth, bullnose, standard tile, 1.0 HP, C-900, 4' swimout, dedicated vac-line; +12" raised Spa, 2 pump system, 4 function air switch, 2 HP blower, 75W light, 5 Jets, 24" Spillway, H-155 heat pump; Diamond Brite in White, Ivory, or Blue

[Andy Pope]

Hi,

Yes it's the 255 limit. This limit also applies when doing the find manually.

This is the most text you can search will

Up to 84 L.F. (15' x 30'), 3' to 5' depth, bullnose, standard tile, 1.0 HP, C-900, 4' swimout, dedicated vac-line; +12" raised Spa, 2 pump system, 4 function air switch, 2 HP blower, 75W light, 5 Jets, 24" Spillway, H-155 heat pump; Diamond Brite in White

You should load the cells content in to a variable and then truncate that to 255.
The use find with the relatively short text

[mdmackillop]

You could search for the first 255 characters, then search the found cells for the next 255 and so on.......

[malik641]

Cool, thanks guys.


As far as my coding goes...I realized that there are cases where more than one cell with the same text (my example being one of them I believe). I realized that even if I could use more than 255 characters, I may still have errors. So I went another route...

[mdmackillop]

A tourist stopped an Irishman to ask for directions to Dublin. "Well", said the worthy man, "If I were you, I wouldn't start from here!"

[Zack Barresse]

I wouldn't trust the xlCellTypeLastCell either...

[vba]Dim LastCell as Long
LastCell = ContractorWS.Range("A:A").Find("*", after:=ContractorWS.Cells(1, 1), _
lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlprevious).row
Set cell = ContractorWS.Range("A1:A" & LastCell).Find(What:=txtPackage.Text, _
LookIn:=xlValues)[/vba]

[mdmackillop]

You'd have thought someone with 3,000+ posts would have come across line breaks!

[Zack Barresse]

ROFL! There!

[mdmackillop]

Thanks Zack, I'll cancel my order for a 42" wide screen.

[malik641]

I wouldn't trust the xlCellTypeLastCell either...

Why not?

[Zack Barresse]

Sometimes Excel gets it scewed up. Do you have ASAP Utilities? There is a function in there to reset Excel's LastCell. The input/output (deletion) of data will *confuse* Excel sometimes. Using the Find("*") method is probably the most sure way to get the last row with data.

[malik641]

Thanks Zack. Always helps to know a more efficient way of coding / working

[Zack Barresse]

No problem Joseph. Keep in mind, there are some who disagree with me, that is my (trying to be humble) opinion, albeit a strong one.

[mdmackillop]

Keep in mind, there are some who disagree with me

[Zack Barresse]

Okay, okay, call me the black sheep of the bunch. I'm young/dumb and I voice my opinion. Hehehe.