How to Nest Loop in Excel Script in order to Scan specific Range

Question

i'm converting a VBA Macro to a Excel Script in order to run a macro at Excel Web

The VBA Code:

//Parameters is a Sheet who has startCol/endCol Cell number and startRow/endRow numbers
// Cells 3,4 is startCol; Cells 3,5 is endCol
// Cells 3,2 is startRow; Cells 3,3 is endRow

For column = Sheets("Parameters").Cells(3,4) To Sheets("Parameters").Cells(3,5)
 For row = Sheets("Parameters").Cells(3,2) To Sheets("Parameters").Cells(3,3)
  
    //then it scan the Projects Sheet and erase the values
      Sheets("Projects").Cells(row, column) = ""
     
    Next row
Next column

How can I convert this to a nested loop in Javascript/Excel Script?

I've tried something like:

let usedRange = workbook.getWorksheet("Parameters").getRange("O5:GW40");
let rowCount = usedRange.getRowCount();
let columnCount = usedRange.getColumnCount();

for(let i = 1; i< columnCount; i++){
  for(let j = 1; j < rowCount; j++){
   if (usedRangeValues[i][j] != ""){
     usedRangeValues[i][j].setValue("");
  }
 }
}

But i'm not getting any step further...

score 2 · Accepted Answer · answered Jun 03 '21 at 18:13

Thanks for your question! If you are just trying to clear values of a range, you can use the following line of code:

let usedRange = workbook.getWorksheet("Parameters").getRange("O5:GW40").setValue("");

If you need to iterate through a range to make a check, you can use the below code. Basically, you need to first get the values of the range and iterate through those values using a nested loop. In most cases, you will start your for loops with 0 instead of 1. Also, when you set a value, you set it on a Cell or a Range, not the value array of that range.

function main(workbook: ExcelScript.Workbook) {
  let usedRange = workbook.getWorksheet("Parameters").getRange("A1:C5");
  let rowCount = usedRange.getRowCount();
  let columnCount = usedRange.getColumnCount();
  let usedRangeValues = usedRange.getValues();
  for (let i = 0; i < rowCount; i++) {
    for (let j = 0; j < columnCount; j++) {
      if (usedRangeValues[i][j] != "") {
        usedRange.getCell(i, j).setValue("");
      }
    }
  }
}

Let me know if you need any additional help!

score 0 · Answer 2 · answered Jul 02 '21 at 07:33

To add to Petra's answer, you can also use worksheet.getRangeByIndexes to get the range using the values from the sheet like in your VBA code. The code would look something like this -

let sheet = workbook.getWorksheet('Parameters');
      
// note the getCell is zero Indexed, so the left top corner cell is (0,0). 
// Also the return value needs to be converted to a number
let startCol = sheet.getCell(2, 3).getValue() as number;
let endCol = sheet.getCell(2, 4).getValue() as number;
let startRow = sheet.getCell(2, 1).getValue() as number;
let endRow = sheet.getCell(2, 2).getValue() as number;

let usedRange = sheet.getRangeByIndexes(startRow, startCol, endRow-startRow +1, endCol-startCol +1);
usedRange.setValue("");

How to Nest Loop in Excel Script in order to Scan specific Range

2 Answers2