Excel Script keeps giving error Range merge You cannot perform the requested operation

0 votes

Ï'm writing an Excel Script. Given a selected cell, it should:

  • Insert a row below the selected cell (this works)
  • For all cells of the inserted row which are in a column left of the selected cell, merge it with the cell above it (which is on the selected row). This fails.

The code is:

function main(workbook: ExcelScript.Workbook) {
  const selectedSheet = workbook.getActiveWorksheet();
  const selectedRange = workbook.getSelectedRange();
  if (selectedRange == null) {
    console.log(`No cells in worksheet selected.`);
    return;
  }
  if ( selectedRange.getCellCount() != 1 ) {
    console.log(`More than one cell selected`);
    return;
  }
  // console.log(`Selected range for the worksheet: " ${selectedRange.getAddress()}`);
  // Insert a rwo below the selected cell
  const targetRow = selectedRange.getOffsetRange(1,0).getEntireRow();
  targetRow.insert(ExcelScript.InsertShiftDirection.down)

  // now merge each inserted row's cell left of the selected column with the cell on the selected row (i.e. the one above it)
  const sColNr = selectedRange.getColumnIndex();
  const sRowNr = selectedRange.getRowIndex();
  console.log(`col,row = ${sColNr}, ${sRowNr}`);
  for ( let c = 0; c < sColNr ; c++ ) {
    let r = selectedSheet.getRangeByIndexes(sRowNr, c, 2, 1);
    r.merge(false);
  }
}

Problem: I can set values in those cells, so I know I have the correct cells in the for loop.

No matter which cell I choose on a blank page, the script always fails with the message "Line 23: Range merge: You can not perform the desired action."

It works when I manually pick the cells and use the Excel Interface to combine them.

Feb 23, 2023 in Others by narikkadan
• 63,720 points
907 views

1 answer to this question.

0 votes

Range.merge() Operation is not possible in ExcelScript on desktop Excel... It is though on office.com/excel.

Code had another tricky functional bug. solved that too. Final code:

    function main(workbook: ExcelScript.Workbook) {
      const selectedSheet = workbook.getActiveWorksheet();
      const selectedRange = workbook.getSelectedRange();
      if (selectedRange == null) {
        console.log(`No cells in worksheet selected.`);
        return;
      }
      // Insert a rwo below the selected cell
      console.log(`Selected range for the worksheet: " ${selectedRange.getAddress()}`);
       //get 1 based indexes of lower left cell of selection
      const sColNr = selectedRange.getColumnIndex();
      const sRowNr = selectedRange.getRowIndex() + selectedRange.getRowCount() - 1;
      console.log(`reference cell = ${sColNr}, ${sRowNr}`)
      const targetRow = selectedSheet.getRangeByIndexes(sRowNr+1,0,1,1).getEntireRow();
      targetRow.insert(ExcelScript.InsertShiftDirection.down)
    
      // now merge each inserted row's cell left of the selected column with the cell on the selected row (i.e. the one above it)
      console.log(`col,row = ${sColNr}, ${sRowNr}`);
      for ( let c = 0; c < sColNr ; c++ ) {
        r = selectedSheet.getRangeByIndexes(sRowNr, c, 2, 1);
        r.merge(false);
      }
    }
answered Feb 23, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

ERROR: You need elevated Administrator privileges in order to run this script.

Hi@akhtar, I guess you are trying to run ...READ MORE

answered Sep 8, 2020 in Others by MD
• 95,440 points
1,827 views
0 votes
1 answer

Git error: "Please make sure you have the correct access rights and the repository exists"

Your git URL might have changed. Change ...READ MORE

answered Feb 17, 2022 in Others by Aditya
• 7,680 points
10,058 views
+15 votes
2 answers

Git management technique when there are multiple customers and need multiple customization?

Consider this - In 'extended' Git-Flow, (Git-Multi-Flow, ...READ MORE

answered Mar 27, 2018 in DevOps & Agile by DragonLord999
• 8,450 points
3,709 views
+2 votes
1 answer
0 votes
1 answer

Error Trying to Display The Names of All Excel Data Models in a Workbook

It is necessary to list ThisWorkbook.model. Not ...READ MORE

answered Apr 10, 2023 in Others by Kithuzzz
• 38,010 points
371 views
0 votes
1 answer

Excel formula to get certain cell if the value is between 2 numbers

So, first with vlookup(): A formula so you ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
873 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP