Part of my macro inserts a formula into a cell and then copies it down to by a number of rows. The number of rows changes from sheet to sheet based on the content of one of the columns. I don’t want to copy it down to the bottom of the sheet as there is other content below my main table.

How do I count the number of rows in a give column (that contains content) and then apply that number to the range that I want to copy the formula down by?

This code returns the correct number of rows…

function AutoNotes3() {
  const sheet = SpreadsheetApp.getActiveSheet();

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;

  console.log(lastRow);

var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('AW8').activate();
  spreadsheet.getCurrentCell().setFormula([lastRow]-2); // the -2 account for the extra content below the range that I want to exclude

  
};

I want to use that number as a reference in a range so I tried this…

function AutoNotes4() {
  const sheet = SpreadsheetApp.getActiveSheet();

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;

  console.log(lastRow);

var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('AW8').activate();
  spreadsheet.getCurrentCell().setFormula('=VLOOKUP(B8,INDIRECT(Settings!$A$3&"!$B$8:$AW"), 48, FALSE)');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('AW8:AW&[lastRow]-2'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

};

When I run the script it can’t apply the range.