Formula in macro not working

Hi.
Can anyone fix this formula I have in the macros so that it can work properly?

This is the outcome I want the formula below to do: In the 2nd to last column, starting from the 18th row, I would like to multiply the 3rd to last column by the value in cell A1.

Dim lr As Long
Dim lc As Long

lr = ActiveSheet.UsedRange.Rows.Count - 2
lc = ActiveSheet.UsedRange.Columns.Count

Range(Cells(18, lc - 1), Cells(lr - 1, lc - 1)).Formula = "=(a1*"&Range(Cells(18, lc - 3), Cells(lr - 1, lc - 1))")"

Any help would be greatly appreciated.

Thank s in advance.

Thanks.
I tried the code but the 'formula bar' in the spreadsheet shows this =(A1*"&Range(Cells(18, lc - 3), Cells(lr - 1, lc - 1))") and the relevant cell shows this #VALUE!

Sure. Copy of Row Columns.xlsm

Thanks.

Also, I can get this formula to work if I use the R1C1 method but the problem with this is that cell A1 (which has the value I want) changes to ascending cells (A2, A3, A4 A5 etc) when the formula is dragged down the cells.