PDA

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



malik641
08-15-2006, 07:32 PM
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):

Set cell = ContractorWS.Range("A1", ContractorWS.Cells.SpecialCells(xlCellTypeLastCell).Address) _
.Find(What:=txtPackage.Text, LookIn:=xlValues)
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
08-16-2006, 04:05 AM
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
08-16-2006, 04:38 AM
You could search for the first 255 characters, then search the found cells for the next 255 and so on.......

malik641
08-16-2006, 07:59 PM
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
08-17-2006, 12:27 AM
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
08-17-2006, 11:20 AM
I wouldn't trust the xlCellTypeLastCell either...

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)

mdmackillop
08-17-2006, 11:23 AM
You'd have thought someone with 3,000+ posts would have come across line breaks! :devil2:

Zack Barresse
08-17-2006, 11:32 AM
ROFL! There! :rofl:

mdmackillop
08-17-2006, 11:35 AM
Thanks Zack, I'll cancel my order for a 42" wide screen.

malik641
08-17-2006, 01:33 PM
I wouldn't trust the xlCellTypeLastCell either...

Why not?

Zack Barresse
08-17-2006, 01:40 PM
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
08-17-2006, 03:33 PM
Thanks Zack. Always helps to know a more efficient way of coding / working :friends:

Zack Barresse
08-17-2006, 03:36 PM
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
08-17-2006, 04:35 PM
Keep in mind, there are some who disagree with me ;)
:rotflmao:

Zack Barresse
08-18-2006, 07:43 AM
Okay, okay, call me the black sheep of the bunch. I'm young/dumb and I voice my opinion. Hehehe. :devil2: