CODEWITHSUNDEEP
office-scripts
Rosenz5
Rosenz5

Reputation: 59

Office Scripts - AutoFill

I am trying to make the column next to another one filled with zeroes when there are values in the column next to it. They become endless though and just go on to infinity. Does anyone know how to make it stop when the column next to it ends as well?

My code:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.getRange("U4").setValue("0");
    selectedSheet.getRange("U4").autoFill();
}

In this picture, I want the column with zero to only to fill the column where there are numbers to the left, right now it fills the whole column, with no end in sight. Is this possible?

enter image description here

Upvotes: 0

Views: 1270

Answers (1)

Brian Gonzalez
Brian Gonzalez

Reputation: 1356

Here's a script that does this using autofill:

    function main(workbook: ExcelScript.Workbook) {
        let selectedSheet: ExcelScript.Worksheet = workbook.getActiveWorksheet();
        selectedSheet.getRange("U4").setValue("0");
        let fillRange: ExcelScript.Range = selectedSheet.getRange("T4").getExtendedRange(ExcelScript.KeyboardDirection.down).getOffsetRange(0,1);
        selectedSheet.getRange("U4").autoFill(fillRange.getAddress(), ExcelScript.AutoFillType.fillDefault);
    }

Alternatively, once you get the filledRange, you can just use the setValue() method to set the values in that range to zero:

    function main(workbook: ExcelScript.Workbook) {
      let selectedSheet: ExcelScript.Worksheet = workbook.getActiveWorksheet();
      let filledRange: ExcelScript.Range = selectedSheet.getRange("T4").getExtendedRange(ExcelScript.KeyboardDirection.down).getOffsetRange(0,1);
      offsetRange.setValue(0);
    }

Upvotes: 2

Related Questions