CODEWITHSUNDEEP
c#excel
Brandon
Brandon

Reputation: 1098

Excel.Range.Find using a string longer than 255 characters

I'm storing strings with 1023 characters each inside of Excel. At a later point I need to be able to search through the excel file and find what row a value is in. I can guarantee that exactly one occurrence of the string I'm looking for will exist somewhere in the workbook.

Currently I get a type Mismatch error every time I try to search (due to the length limit of 255 on the find function). When I replace my search string with something short like "teststring", it works fine. How can I search for a 1023 character string in Excel?

foreach (string missingItem in missingItems)
{
    Range currentFind = null;
    foreach (Worksheet searchSheet in oWB.Worksheets)
    {
        Range lookAtRange = (Range)searchSheet.get_Range ("A1", "F20");

        currentFind = lookAtRange.Find (
            missingItem, 
            Missing.Value, 
            XlFindLookIn.xlValues,
            Missing.Value,
            Missing.Value, 
            XlSearchDirection.xlNext, 
            false, 
            false,
            Missing.Value);

        if (currentFind != null)
        {
            Range deleteRow = (Range)searchSheet.Rows[currentFind.Row];
            deleteRow.Delete (XlDirection.xlUp);
            break;
        }
    }
}

Upvotes: 2

Views: 3687

Answers (1)

Stepan1010
Stepan1010

Reputation: 3136

http://www.vbaexpress.com/forum/archive/index.php/t-9179.html

In short, if all your strings are unique for the first 255 characters, you don't need to worry about the rest.

You just need to truncate the string before you perform a find on it:

missingItem = Left(missingItem,255)

or alternatively: 

missingItem = Right (missingItem,255)

Otherwise, if there are many duplicates of the first 255 characters, you could divide the string by 255 - then round up - then split the original string into seperate strings, search them individualy - does that help you?

EDIT:

Otherise, here is how you can check a string for equivalency for each cell in a range: http://www.vbaexpress.com/kb/getarticle.php?kb_id=167

Sub FindIt()
    Dim Cell As Range
    Dim SearchString As String
    For Each Cell In Range("A1:D500")
        If Cell.Value = "dqwlokjdowqijowqijdoinowiqhdoiwqiophiruegnqpiunrgpiuqgnrgkjffndskfiougpiodghiudhfgtothisansdfldkjflsdffjlksdjflksjfdoiejwfoiwjeoinfoinewoifjwoiejfoiwejfoiwejfoijjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjwerkjwelkfjwelkjflkwejlfkjwelkjflwkjeflknweoifnoweinfoinewfoinoifwnwoienfoinweoddddddddddddddddddddddddddddddddddddddddfinwoioiefldkjsfoijoneiojfoijfewwefweeeeeeeeeeeefwef" Then '<< use "Like" for wildcards in If-Then statements
            MsgBox "An ''it'' was found at " & Cell.Address & " (" & Cell & ")"
        End If
    Next Cell
End Sub

Funny Search String

And yes I did pretend to type that as something important as my boss walked by. I don't think he was fooled though - since I just mashed it out on my keyboard.

Upvotes: 2

Related Questions