This project has moved and is read-only. For the latest updates, please go here.

Not able to copy the cell formula

Aug 26, 2015 at 2:25 PM
Hi,
I am trying to copy rows from one sheet to another workbook sheet. some of the cells contain formulae which I want them to be updated as per the new sheet's relative location.

However,setting the cell's formula or FormulaR1C1 property does not seem to work.
here is what I am doing to copy the row.
                                       {
                                           string  emp_id = srcWorksheet.Cells[i,1].Value.ToString();
                                           Console.WriteLine("Employee ID= "+emp_id);
                                           if(i%2==0)
                                           {
                                            worksheet.InsertRow(row,1);
                                               for(int col=1;col<endCol;col++){
                                                if(srcWorksheet.Cells[i,col].FormulaR1C1!=null && srcWorksheet.Cells[i,col].FormulaR1C1.Length>0)
                                                    worksheet.Cells[row,col].FormulaR1C1=srcWorksheet.Cells[i,col].FormulaR1C1;
                                                    else
                                                    worksheet.Cells[row,col].Value=srcWorksheet.Cells[i,col].Value;
                                               }
                                               row++;
                                               
                                               
                                            }   
                                       }
Original formula:
IF(OR(W100="USA",W100="IRL"),VLOOKUP(G100,Guidance!$B$16:$C$25,2,0),VLOOKUP(G100,Guidance!$B$16:$D$25,3,0))

Formula in the copied cell:
=IF(OR(W13="USA",W13="IRL"),VLOOKUP(G13,$B$179:$C$188,2,0),VLOOKUP(G13,$B$179:$D$188,3,0))

It looks like the reference to the sheet is lost and the formula is corrupted in the process.

Could anybody please let me know how I can solve this issue?

Thanks in advance.