Search code examples
excelexcel-2007array-formulasvba

Array formulas and PasteSpecial:=Formats

I have this small issue in a workbook. I wanted to simply a very long function by using array formulas. The formula works well and produces the expected results. However, in my VBA macro I copy and then I do a formula paste and then a format paste. The format paste crashes with : "Impossible to paste because copy and paste zones are of different size" (or similar to that). This problem is solved if I use any other formula that is not an array formula.

Did anyone experience this before and find a proper solution ? I can't seem to find help an answer on Google. I'll post code if needed, but it is rather straight forward (Row.Copy ... Rows(something, something).PasteSpecial...) and works with a non-array formula anyway.

Edit: The code:

 sRapDetail = "rap - détails"

    Sheets(sRapDetail).Select
    iStart = 17
    iFormuleExceptionRNIAC = 13
    iFormule1 = 14
    iFormule2 = 15
    iFormuleExceptionAR = 16
    range(Rows(iStart - 1), Rows(1000)).Hidden = False

    iLastRow = Cells(65535, "B").End(xlUp).Row
    range("A" & iStart & ":AL" & iLastRow).Select
'    selection.Borders(xlInsideVertical).LineStyle = xlNone
'    selection.Borders(xlEdgeBottom).LineStyle = xlNone
    selection.Delete Shift:=xlUp

    'RNIAC
    Sheets("Zoom0").Select

    If Cells(21, "B").Value = "" Or Cells(22, "B").Value = "" Then
        iLastRow = 21
    Else
        iLastRow = Cells(21, "B").End(xlDown).Row
    End If
     iNbRow = iLastRow - 20

    Sheets(sRapDetail).Select
    Rows(iFormuleExceptionRNIAC).Select
    selection.Copy
    range(Rows(iStart), Rows(iStart - 1 + iNbRow)).Select
    selection.PasteSpecial xlPasteFormulas
    selection.PasteSpecial xlPasteFormats

(And yes I know the use of selection is ugly (or at least in this case) but this was written by a colleague)

Solution

  • The problem occurs because you cannot copy an array formula on top of itself.

    For example, if you have an array formula in cell A1 and you want to copy it down to cell A10, you can't copy A1 and highlight A1:A10 and paste. It won't work. You have to copy A1, then highlight A2:A10.

    That said, the problem most likely exists in the fact that the row from Rows(iFormuleExceptionRNIAC) probably intersects with this range(Rows(iStart), Rows(iStart - 1 + iNbRow))

    If you can ensure they don't intersect, copying the array formulas will work.