Forum Discussion

Anish_Srivastava2002's avatar
Anish_Srivastava2002
Copper Contributor
Jul 06, 2023

Excel Office Scripts - This formula will not run automatically does fine manually.

Good afternoon,  I am working on automating a massive data sheet (regenerates every month) and I have gotten everything to work so far except this one step.    After all the automation so far its ...
Macros and VBA
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 06, 2023

Anish_Srivastava2002 

Here recorder generates the script with relative references in RC notation, like

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range M2 on selectedSheet
	selectedSheet.getRange("M2").setFormulaLocal("=IFERROR(INDEX(R2C5:R10000C5, SMALL(IF(R2C9=R2C4:R10000C4, ROW(R2C5:R10000C5)-1,\"\"), ROW()-1)),\"\")");
	// Auto fill range
	selectedSheet.getRange("M2").autoFill("M2:M14", ExcelScript.AutoFillType.fillDefault);
}

Thus wrong range reference appears. Correct in the code formula manually, like

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range M2 on selectedSheet
	selectedSheet.getRange("M2").setFormulaLocal("=IFERROR(INDEX($E$2:$E10000, SMALL(IF($I2=$D$2:$D10000, ROW($E$2:$E10000)-1,\"\"), ROW()-1)),\"\")");
	// Auto fill range
	selectedSheet.getRange("M2").autoFill("M2:M14", ExcelScript.AutoFillType.fillDefault);
}

it shall work. At least in that part.

Resources