Excel.Range.Find 使用长度超过 255 个字符的字符串

我在 Excel 中存储每个包含 1023 个字符的字符串。稍后，我需要能够搜索 Excel 文件并找到某个值所在的行。我可以保证我正在查找的字符串在工作簿中的某处恰好出现一次。

目前，每次尝试搜索时，我都会收到类型不匹配错误（由于查找函数的长度限制为 255）。当我用“teststring”之类的短字符串替换搜索字符串时，它工作正常。如何在Excel中搜索1023个字符的字符串？

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;
        }
    }
}

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

简而言之，如果所有字符串的前 255 个字符都是唯一的，则无需担心其余部分。

您只需要在执行查找之前截断字符串即可：

missingItem = Left(missingItem,255)

或者：

missingItem = Right (missingItem,255)

否则，如果前 255 个字符有很多重复项，您可以将字符串除以 255 - 然后向上舍入 - 然后将原始字符串拆分为单独的字符串，单独搜索它们 - 这对你有帮助吗？

EDIT:

另外，以下是如何检查字符串与范围内每个单元格的等效性的方法：http://www.vbaexpress.com/kb/getarticle.php?kb_id=167 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

是的，当我的老板走过时，我确实假装将其视为重要的事情。但我不认为他被愚弄了——因为我只是在键盘上把它捣碎了。