I am working on trying to automate the length of an autofill based on the value in cell C6. The cell C6 looks at row 9 and finds the last instance column where there is a value with the following formula
=SUBSTITUTE(ADDRESS(1,LOOKUP(1E+307,COLUMN(9:9)*IF(9:9=””,””,1)),4),”1″, “”)

I have the following code linked to the button:

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();

//part that isnt working
Dim val as Long
val = Range(“C6”).Value

// Auto fill range
    selectedSheet.getRange("D11:D26").autoFill("D11:'val'26"), ExcelScript.AutoFillType.fillDefault;

}

I would like the script to pull the value from C6 which will be a column lettering that is 3 leters, such as MXT and insert it where i have typed ‘val’ in the auto fill section.

i tried to use

Dim val as Long
val = Range(“C6”).Value

and val as my variable in the autofill portion.

TYIA