none
Interop.Excel.WorksheetFunction.Match() misunderstanding. RRS feed

  • Question

  • Hi!

    What-I-want-to part:

    Loop through Excel.Worksheets with foreach cycle to loop through every Excel.Worksheet to get some data out of it.

    The data I'm going to extract is only going to be extracted if current worksheet contains some_text somewhere.

    In some cases

    Excel.Range range = worksheet.UsedRange;
    Excel.Range find = null;
    find = range.Find( "some text", Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing );

    Find() does not work properly with Excel.Range variable because some_text has some unknown (for me) formatting inside the Excel document and also does not seem to be found by =SEARCH("some text", E:E) in the existing Excel document (the function returns an error of wrong variable).

    What-I-came-to part:

    Excel.WorksheetFunction wsf = app.WorksheetFunction;
    //double f = wsf.Match("*some_text*", ?? );

    It looks like some worksheets of my Excel document contain the proper formatted some_text so I am able to Find() it.

    But another worksheets do not. I decided to look for a substring to not to deal with unknown characters in the document.

    The only found solution was Match() function. It looks like =MATCH("*some text*";E:E).

    I am not clear with this article

    https://msdn.microsoft.com/en-us/library/office/ff835873.aspx

    to insert second argument as well as to use it for current worksheet programmatically. (Also how could I insert into second argument Excel.Range variables or at least E:E-like ranges?)

    So I would want to see proper using of Match() in my case or any other ideas of solving if such exist.

    UPD: well, guess I can retrieve every cell.Value2 to IndexOf("some_text") in c# project itself but.. it is a bit wrong, isn't it?

    UPD2: ok. =MATCH("*some text*";E:E) does not work too for some cells with some_text in either. I'm stuck. Can't get what is wrong with those cells. In addition this variant of function returns N/A if cell contains some_text without any prefixes and suffixes.

    UPD3: Solved. It does not answer the topic but it solves the problem. The problem was in cells values as mentioned. In my case the search text consists of two words separated by space. It seems the authors used some different space characters to fill it. so I've tried to use =SEARCH("some*text", E:E) instead of just =SEARCH("some text", E:E) and get my results.

    • Edited by Olta11 Thursday, February 12, 2015 3:13 PM
    • Moved by CoolDadTx Thursday, February 12, 2015 3:28 PM Office related
    Thursday, February 12, 2015 1:30 PM

Answers

  • Hi,

    UPD3: Solved. It does not answer the topic but it solves the problem. The problem was in cells values as mentioned. In my case the search text consists of two words separated by space. It seems the authors used some different space characters to fill it. So I've tried to use =SEARCH("some*text", E:E) instead of just =SEARCH("some text", E:E) and get my results.

    Thanks for sharing your solution.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 13, 2015 6:30 AM
    Moderator

All replies

  • If you were using Match, you need to set the sorted parameter to False

    =MATCH("*some text*";E:E,False)

    Thursday, February 12, 2015 4:29 PM
  • Hi,

    UPD3: Solved. It does not answer the topic but it solves the problem. The problem was in cells values as mentioned. In my case the search text consists of two words separated by space. It seems the authors used some different space characters to fill it. So I've tried to use =SEARCH("some*text", E:E) instead of just =SEARCH("some text", E:E) and get my results.

    Thanks for sharing your solution.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 13, 2015 6:30 AM
    Moderator