Hello i am trying to paste a formula into a cell, then copy and paste that formula a few columns down but i am getting a run time error 1004 in my code and not sure why or what needs to be edited
I am getting a run time error 1004 method range of object worksheet failed error here at:
page1.Range(2, (((i - 1) * (ColAmt)) + colNum) + colOff).Paste
VBA Code:
Sub Formula_change()
Dim rowNum, ColAmt, colNum, colOff, i As Integer
rowNum = 44
ColAmt = 4
colNum = 4
colOff = 2
i = 2
Dim main As Workbook
Set main = Application.ThisWorkbook
Dim main_ws As Worksheet
Dim page1 As Worksheet
Set page1 = main.Worksheets("Page 1")
page1.Range("F2").FormulaR1C1 = "=IF(IFERROR(VLOOKUP(R[-37]C[-1],'List'!R4C7:R200C18,12,FALSE),0)=""Please fill out"",""--"",IFERROR(VLOOKUP(R[-37]C[-1],'List'!R4C7:R200C18,12,FALSE),0))"
Application.CutCopyMode = False
page1.Range("F2").Copy
page1.Range(2, (((i - 1) * (ColAmt)) + colNum) + colOff).Paste
I am getting a run time error 1004 method range of object worksheet failed error here at:
page1.Range(2, (((i - 1) * (ColAmt)) + colNum) + colOff).Paste