Range.Find - Type mismatch. when searching for long strings

0

In C# VSTO for excel, When I use the Find method on very long strings I get:

Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

Any Ideas?

vsto
excel-interop
asked on Stack Overflow Feb 19, 2018 by yossico

1 Answer

0

After playing a bit with Excel & googling I've found out that there is a limitation of 255 characters to Find - you can press CTRL-F and see that the input is limited.

I couldn't find Microsoft documentation for that limitation. so here is a workaround extension method in C#:

   public static Range FindLongStr(this Range rng, 
                                        string what,
                                        object after,
                                        object lookIn, 
                                        object lookAt, 
                                        object searchOrder, 
                                        XlSearchDirection searchDirection = XlSearchDirection.xlNext,
                                        object matchCase = null,
                                        object matchByte = null,
                                        object searchFormat = null){
       Range retVal;
       if (what.Length < 255)
       {
          retVal = rng.Find(what, after, lookIn, lookAt, searchOrder, searchDirection, matchCase, matchByte, searchFormat);
       }
       else
       {
          var whatSearch = what.Substring(0, 254) + "*";
          retVal = rng.Find(whatSearch, after, lookIn, lookAt, searchOrder, searchDirection, matchCase, matchByte, searchFormat);
          while (retVal != null && retVal.Cells.Value2 != what)
          {
             retVal = rng.Find(whatSearch, retVal, lookIn, lookAt, searchOrder, searchDirection, matchCase, matchByte, searchFormat);
          }
       }
       return retVal;
    }
answered on Stack Overflow Feb 19, 2018 by yossico

User contributions licensed under CC BY-SA 3.0