Excel Paste Special and Add Operation

Question

When we want add a Number (for example 5) to all of excel worksheet cells, you can copy cell containing the value 5, select range of other cells we want (for example a 10x10 Range) and Right click-> Paste special then check add Operation and click OK.

I want to add 5 to all of cells in selected range with Excel Interop dll in C#. How can this be achieved?

Answer 1

To perform a Paste Special -> Add operation is fairly easy. Assuming you already have a Worksheet object the following will work:

// Copy the initial value from cell A1

xlWorksheet.get_Range("A1", "A1").Copy(Missing.Value);

// Paste special (with Addition) the value over cells A2 to J11

xlWorksheet.get_Range("A2", "J11").PasteSpecial(Excel.XlPasteType.xlPasteAll,
    Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);

You can find a full explanation of the PasteSpecial method here.