How Can I get my excel script to continue until last cell with a numeric value?

Question

I created a script. However, it starts at A2 and ends at A9. When I included a number in A10 it didnt populate. How can I ge tthis script to be versatile?

For example, I may just have numeric values between A2:A4 or I may have A2:A11. Is it this possible? I have included my current script for review.

        function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Set range C3:F9 on selectedSheet
           selectedSheet.getRange("C3:F9").setFormulasLocal
    ([["=SUM(C2*A3)","=SUM(D2*A3)","=SUM(E2*A3)","=SUM(F2*A3)"],  ["=SUM(C2*A4)","=SUM(D2*A4)","=SUM(E2*A4)","=SUM(F2*A4)"],["=SUM(C2*A5)","=SUM(D2*A5)","=SUM(E2*A5)","=SUM(F2*A5)"],["=SUM(C2*A6)","=SUM(D2*A6)","=SUM(E2*A6)","=SUM(F2*A6)"],["=SUM(C2*A7)","=SUM(D2*A7)","=SUM(E2*A7)","=SUM(F2*A7)"],["=SUM(C2*A8)","=SUM(D2*A8)","=SUM(E2*A8)","=SUM(F2*A8)"],["=SUM(C2*A9)","=SUM(D2*A9)","=SUM(E2*A9)","=SUM(F2*A9)"]]);
}

I haven't tried anything as of yet. I am still researching how to fix it.

Answer 1

• lastRow is row# of the non-blank cell on Col A
• Use setFormulaLocal to apply formulas on C3:Fx (x=lastRow)

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let lastRow = selectedSheet.getRange("A:A").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
    selectedSheet.getRange("C3:F" + lastRow).setFormulaLocal("=SUM(C$2*A3)");
}

---

Update:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let useRange = selectedSheet.getUsedRange();
    let formulaRange = useRange.getOffsetRange(2,2).getResizedRange(-2,-2);
    // console.log(formulaRange.getAddress());
    formulaRange.setFormulaLocal("=SUM(C$2*$A3)");
}