Set value in a excel worksheet to equal the length of the previous column in C#

Hi,

pls try something like this

string s = "=LEN(RC[-1])";
Range rng1 = sheet.get_Range(sheet.Cells[7, 2], (sheet.Cells[7, 2] as Excel.Range).get_End(Excel.XlDirection.xlDown)).Offset[0,1];
rng1.FormulaR1C1 = s;
rng1.Value2 = rng1.Value2;

Select allOpen in new window

Regards

What is value1 and value2 here ?

.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)

.Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.

Using .Value is usually a bad idea because you may not get the real value from the cell, and they are slower than .Value2

So the first time the excel is saved the value gets saved correctly. If I open the excel and edit a cell the length column does not get updated.

Thanks,
Aditya

pls try

        string s = "=LEN(RC[-1])";
        Rng1= (Excel.Range)xlWorkSheet.get_Range(xlWorkSheet.Cells[2, 7] as Excel.Range, (xlWorkSheet.Cells[2, 7] as Excel.Range).get_End(Excel.XlDirection.xlDown)).Offset[0,1];
        Rng1.FormulaR1C1 = s;
        Rng1.Value2 = Rng1.Value2;

Select allOpen in new window

THis is the code that I have.
 string s = "=LEN(RC[-1])";
                               Microsoft.Office.Interop.Excel.Range rng1 = wsheet.get_Range(wsheet.Cells[2, 7], (wsheet.Cells[dsMDOID.Tables[0].Rows.Count + 1, 7] as Excel.Range).get_End(Excel.XlDirection.xlDown)).Offset[0, 1];
                               rng1.FormulaR1C1 = s;
                               rng1.Value2 = rng1.Value2;

Also even if  I have 20 rows in the sheet it seems to apply the formula to around 65K rows.

The code you posted seems to be similar to what I had.

Thanks,
Adittya

get_end removed