#78 Error 13 Type mismatch if cells are more than 255 chars in key column

[dominique delamarre]

Hi

I am using the database type search
I found an issue in frmMain / findMatch when cells have more than 255 chars in the key column, then the range.find function issues an error 13 type mismatch

I workaround for the moment with the below change.
Maybe there is a better solution

Regards

---

...
with Worksheet2.UsedRange.Columns(lSearchColumn)
If LastPosition Is Nothing Then
' DD 20160127 manage > 255 data to search breaking find
' assuming a match of the leftmost 254 chars is enough
If Len(Worksheet1.Cells(lSearchRow, lSearchColumn)) < 255 Then
Set c = .Find(Worksheet1.Cells(lSearchRow, lSearchColumn), _
LookIn:=xlValues, LookAt:=xlWhole)
' Set c = .Find(Worksheet1.Cells(lSearchRow, lSearchColumn), _
' LookIn:=xlFormulas, LookAt:=xlWhole)
Else
Set c = .Find(Left(Worksheet1.Cells(lSearchRow, lSearchColumn), 254), _
LookIn:=xlValues, LookAt:=xlPart)
End If

    Else
         ' DD 20160127 manage > 255 data to search breaking find
         If Len(Worksheet1.Cells(lSearchRow, lSearchColumn)) < 255 Then
            Set c = .Find(Worksheet1.Cells(lSearchRow, lSearchColumn), LastPosition, _
                    LookIn:=xlValues, LookAt:=xlWhole)
            '         Set c = .Find(Worksheet1.Cells(lSearchRow, lSearchColumn), LastPosition, _
                      '         LookIn:=xlFormulas, LookAt:=xlWhole)
        Else ' more than 255
            Set c = .Find(Left(Worksheet1.Cells(lSearchRow, lSearchColumn), 254), LastPosition, _
                    LookIn:=xlValues, LookAt:=xlPart)
        End If
    End If

    _____________________________________________

[SteveT]

Thanks. I have made the same change to my version. I thought about performing a better check that would iterate through and matches <254 in case there are multiple keys with the same 255 starting chars, but that seems very unlikely, so will probably keep your version!

Steve