Question 255503 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – office script replace formula with value

please advice best way to replace formulas with values.
I used before:

//copy-paste values
rEPORT__2_.getRange().copyFrom(rEPORT__2_.getRange(), ExcelScript.RangeCopyType.values, false, false);

but it stopped to work starting from today. Excel just get unexpected error if i try to copy-paste more tnan 100 rows in one script.
So, is there any other way to replace formalas with values at whoole sheet ?

I tried to use loop (for i). But result almost the same – it process about 50-100 rows with copy-paste and then i just get an error (time-out or unexpected).

Tags:

2

Answers


  1. 0
    • Range("A1").CurrentRegion.Select
    • Selection.Copy
    • Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    • Step1: define the region to copy by choosing a cell inside the continuous range.
    • Step2: Copy the selected range.
    • Step3: Paste values while still selecting the copied range to replace formulas with values.
    Login or Signup to reply.
  2. 0

    There’s an effective method for transforming formulas into fixed values.

    • Obtain the intersection range to minimize the size of the target range.
    • Use the setValues function for conversion, as it is more efficient than copyFrom.
        const selectedSheet = workbook.getWorksheet("ReportSheet"); // Update name as needed
    let dataRanage = rEPORT__2_.getRange().getintersection(selectedSheet.getUsedRange());
    dataRanage.setValues(dataRanage.getValues());
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search