пытаюсь немного допилить чужой профессионально сделанный код в vba. Задача добавить ф-цию iferror, чтобы избежать ошибок при пересчете. При попытке добавить ф-цию iferror эксель выдает ошибку 1004. Наверное что-то делаю не так.
Ниже исходный код:
Код
' Use a template version of the formula and replace the parameters with the proper values
' Using a template version because the same formula is used in 3 different columns
' Only the Column Id is different in each one
Dim quadrantFormula As String
quadrantFormula = "=INDEX(Analysis!$$$COLUMNID$$$7:$$$COLUMNID$$$$LASTROW$$,SMALL(IF((Analysis!$F$7:$F$$$LASTROWWITHOFFSET$$=""$$QUADRANTCODE$$""),ROW($1:$$$LASTROW$$)),ROW()-$$STARTROWOFFSET$$))"
Dim formulaToUse As String
quadrantFormula = Replace(quadrantFormula, "$$LASTROWWITHOFFSET$$", lastRow + 6)
quadrantFormula = Replace(quadrantFormula, "$$LASTROW$$", lastRow)
quadrantFormula = Replace(quadrantFormula, "$$STARTROWOFFSET$$", startRow)
quadrantFormula = Replace(quadrantFormula, "$$QUADRANTCODE$$", quadrantCode)
' This portion is used to set the Formulae of the Quadrant (starting at 1 row lower than the one above)
' Product Name
formulaToUse = Replace(quadrantFormula, "$$COLUMNID$$", "B")
Set quadrantRange = quadrantSheet.Range(quadrantSheet.Cells(startRow + 1, startCol), quadrantSheet.Cells(startRow + lastRow, startCol))
quadrantRange.FormulaArray = formulaToUse
' ROS
formulaToUse = Replace(quadrantFormula, "$$COLUMNID$$", "D")
Set quadrantRange = quadrantSheet.Range(quadrantSheet.Cells(startRow + 1, startCol + 1), quadrantSheet.Cells(startRow + lastRow, startCol + 1))
quadrantRange.FormulaArray = formulaToUse
quadrantRange.NumberFormat = "0"
' WD
formulaToUse = Replace(quadrantFormula, "$$COLUMNID$$", "E")
Set quadrantRange = quadrantSheet.Range(quadrantSheet.Cells(startRow + 1, startCol + 2), quadrantSheet.Cells(startRow + lastRow, startCol + 2))
quadrantRange.FormulaArray = formulaToUse
quadrantRange.NumberFormat = "0"