Hello, i am getting a run time error we cant do this to merged cells. e29 is merged so using columns E and F and the then what the code is doing is pasting the formula into that cells. Then trying to copy the formula paste it into the cells basically repasting it into e29 when "I" is one then when "I" = 2 it will paste into i29, however I29 is also merge not sure how to get rid of this error
The error is happening here in the copde
page1.Cells(29, (((i - 1) * (entColAmtCol)) + colNum) + colOffFourCol).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
VBA Code:
Sub Formula_change()
Dim rowNum, ColAmt, colNum, colOff, i As Integer
rowNum = 29
ColAmt = 4
colNum = 4
colOff = 1
i = 1
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("E29").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("E29").Copy
page1.Cells(29, (((i - 1) * (entColAmtCol)) + colNum) + colOffFourCol).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
End Sub
The error is happening here in the copde
page1.Cells(29, (((i - 1) * (entColAmtCol)) + colNum) + colOffFourCol).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats