Search Listbox Problem

[suschuck]

My first post, so bear with me. I have a spreadsheet that uses a userform list box to search multiple worksheets and return results in the box. It works just fine until the cell it's listing contains more than 2046 characters, then I get a run-time error indicated that it could not set the list property type, type mismatch.

I can only figure that the cell is too big, but I can't figure out how to get around it. Any ideas?

[Ebrow]

You could set a format to the varable to set its length.

For example:

[VBA]
Dim myMaxLength As Long
Dim myFormat As String
Dim myString As String

myMaxLength = 100
myFormat = String(myMaxLength, "@")
myString = Format(Cells(1, 1).Value, myFormat)
[/VBA]

[suschuck]

Thank you so much for the response, but I'm not great at the whole VBA thing. This is the code for the box. Where would the formatting apply?

Sub Locate(Name As String, Data As Range)
Dim rngFind As Range
Dim strFirstFind As String
Dim strCell As String
Dim strdef As Variant


With Data
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
If rngFind.Row > 1 Then
strCell = rngFind.EntireRow.Cells(1, 2).Value
strdef = rngFind.EntireRow.Cells(1, 4).Value
ListBox1.AddItem rngFind.Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Data.Parent.Name
ListBox1.List(ListBox1.ListCount - 1, 2) = strCell
ListBox1.List(ListBox1.ListCount - 1, 3) = rngFind.Value
ListBox1.List(ListBox1.ListCount - 1, 4) = strdef
ListBox1.List(ListBox1.ListCount - 1, 5) = rngFind.Address
End If
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
End If
End With

End Sub

The red is where it gets hung up on those large cells.

[Charlize]

Sub Locate(Name As String, Data As Range)
Dim myMaxLength As Long
Dim myFormat As String

myMaxLength = 100
myFormat = String(myMaxLength, "@")

Dim rngFind As Range
Dim strFirstFind As String
Dim strCell As String
Dim strdef As Variant


With Data
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
If rngFind.Row > 1 Then
strCell = rngFind.EntireRow.Cells(1, 2).Value
strdef = rngFind.EntireRow.Cells(1, 4).Value
ListBox1.AddItem rngFind.Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Data.Parent.Name
ListBox1.List(ListBox1.ListCount - 1, 2) = strCell
ListBox1.List(ListBox1.ListCount - 1, 3) = rngFind.Value
ListBox1.List(ListBox1.ListCount - 1, 4) = Format(strdef, myFormat)
ListBox1.List(ListBox1.ListCount - 1, 5) = rngFind.Address
End If
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
End If
End With
End Sub

Not tested but you could give this a try.

[suschuck]

You are soooooo my hero!! It works perfectly. Thank you very much.

[Charlize]

Your welcome but all I did was putting everything in a bag, shake it a little and voila, there comes the solution using all the parts.