none
How to get the Cell Address for Particular string in Excel using C#` RRS feed

  • Question

  • Hi team,

    I've one string called "Sub Total" and i want to get this address of this word in the Sheet and also i want to search respected column data as well.

    For ex: I've Sub Total word at B 125, at the same time, i need to get the data from T 125.

    Kindly suggest me how to get this using C#.

    Regards,

    Sureshbabu

    Thursday, July 28, 2016 11:44 AM

Answers

All replies

  • Hi,

    Use Range.Find Method (Excel) & Range.Address property to get the address

    Use WorksheetFunction.VLookup Method (Excel) to get the data

    E.g. The word is in A1 and I get the data from D1

                Excel.Range a = ThisApplication.get_Range("A1:T10");
                Excel.Range b = a.Find("Sub Total", Type.Missing, Excel.XlFindLookIn.xlFormulas,
                    Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows,
                    Excel.XlSearchDirection.xlNext, false, false, false);
                MessageBox.Show(b.Address);
                var c =ThisApplication.Application.WorksheetFunction.VLookup("Sub Total", a, 4, false);
    
                if (ThisApplication.Application.WorksheetFunction.IsError(c))
                {
                    MessageBox.Show("Cannot find");
                }
                else
                {
                    MessageBox.Show(c);
                }



    Thursday, July 28, 2016 5:25 PM
    Moderator
  • Thanks.. Deepak.

    But i need to get the range of the worksheet dynamically and i need to search for a word based on the cell address of word ,, i need to get the another value in another column.

    For Ex: I need to search for a word called, SubTotal, and don't know how many rows and columns are exist in the worksheet (Because the rows and columns data will be change from time to time) so i need to get the dynamic range of the work sheet and in that range i need to find this word. Suppose the word is at B 125, i need to get the value at T 125.

    This is the requirement.

    Kindly help me. 

    Friday, July 29, 2016 10:39 AM
  • Hi,

    Use Range.Find Method (Excel) & Range.Address property to get the address

    Use WorksheetFunction.VLookup Method (Excel) to get the data

    E.g. The word is in A1 and I get the data from A4

                Excel.Range a = ThisApplication.get_Range("A1:T10");
                Excel.Range b = a.Find("Sub Total", Type.Missing, Excel.XlFindLookIn.xlFormulas,
                    Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows,
                    Excel.XlSearchDirection.xlNext, false, false, false);
                MessageBox.Show(b.Address);
                var c =ThisApplication.Application.WorksheetFunction.VLookup("Sub Total", a, 4, false);
    
                if (ThisApplication.Application.WorksheetFunction.IsError(c))
                {
                    MessageBox.Show("Cannot find");
                }
                else
                {
                    MessageBox.Show(c);
                }


    Sor, just found i had written wrong. The word is in A1 and i get the data from D1.
    Friday, July 29, 2016 10:48 AM
    Moderator
  • >>don't know how many rows and columns are exist in the worksheet

     

    You can use Worksheet.UsedRange Property

    Friday, July 29, 2016 10:51 AM
    Moderator
  • Thanks .. HibariRokudo..

    I am getting the null value for that function. PFB the code i've used.

     string strLOrder = Labor_OrderRange(strcellContent, wsOrderLabor);

                    

     private string Labor_OrderRange(string cellContent, ExcelTools.Interop.Excel.Worksheet wsOrderLabor)
            {
                string currentFind = null;

                try
                {
                    Microsoft.Office.Interop.Excel.Range a = wsOrderLabor.get_Range("A1:B1000");
                    Microsoft.Office.Interop.Excel.Range b = a.Find(cellContent, Type.Missing, 
                                                                Microsoft.Office.Interop.Excel.XlFindLookIn.xlFormulas,
                                                                Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
                                                                Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
                                                                Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext,
                                                                false, false, false);

                    var c = wsOrderLabor.Application.WorksheetFunction.VLookup(cellContent, b, 4, false);

                    if (wsOrderLabor.Application.WorksheetFunction.IsError(c))
                    {
                        MessageBox.Show("Cannot find");
                    }
                    else
                    {
                        MessageBox.Show(c);
                    }

                    return currentFind = b.Address;

                }
                catch (Exception ex)
                {
                    LogError(ex);
                }

                return currentFind;
            }

    Friday, July 29, 2016 12:49 PM
  • >>VLookup(cellContent, b, 4, false);

    Surely you get NULL because you are searching in one cell. Besides, 4 is out of the range.

     

    In WorksheetFunction.VLookup Method (Excel), the method searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

    Arg1=Lookup_value

    Arg2=Table_array

    Arg3=Col_index_num: the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

     

    E.g. We find the value in A5, when  Col_index_num=3, expression returns the value in C5.

    Suppose range=B1:T125, the word is at B125, if you want to get value at T125, Col_index_num should be 19.

    Notice:If Col_index_num is greater than the number of columns in table_array, the VLookup method generates an error.

    Friday, July 29, 2016 2:21 PM
    Moderator
  • It seems that I complicate the problem.

    In fact, there is no need to use VLookup since we have got the address of B125.

    Try string v = ((Excel.Range)ThisApplication.ActiveSheet.cells[b.Row, b.Column + 18]).Value;


    Friday, July 29, 2016 3:18 PM
    Moderator