Array Formula Resulting in #REF Error

Question

I am not quite sure why this is occurring, but I cannot seem to identify a solution online. I am merely attempting to copy some formulas from one sheet to another (the formula will .FillDown in the destination sheet), but I am having issues with one formula in particular. I believe it is because the formula is an array, but even when I use .Range.FormulaArray I receive the same #REF! error. Here is the formula and the script that transfers it:

(Note: the references in the formula are accurate for the destination sheet, not the origin sheet - the formula is maintained in a designated sheet because all of the data on the destination sheet is deleted when closing the workbook to reduce file size)

Intended Formula

IF(LEFT(H2,1)="2","Platforms",INDEX('Units - Job Numbers'!$A$2:$A$200,SMALL(IF('Units - Job Numbers'!$B$2:$CO$200=I2,ROW('Units - Job Numbers'!$B$2:$CO$200)-ROW('Units - Job Numbers'!$B$2)+1),1)))

Formula After Transfer

=IF(LEFT(H2,1)="2","Platforms",INDEX('Units - Job Numbers'!#REF!,SMALL(IF('Units - Job Numbers'!#REF!=I2,ROW('Units - Job Numbers'!#REF!)-ROW('Units - Job Numbers'!#REF!)+1),1)))

VBA

'Copy and Extend Raw WIP Data Formulas
With ThisWorkbook.Worksheets("Raw WIP Data")
.Range("AE2:AL2").Formula = ThisWorkbook.Worksheets("Formulas").Range("A7:H7").Formula
.Range("AM2").Formula = ThisWorkbook.Worksheets("Formulas").Range("I7").Formula
.Range("AE2:AM" & WIPLastRow).FillDown
.Range("AE2:AM" & WIPLastRow).Copy
.Range("AE2:AM" & WIPLastRow).PasteSpecial xlPasteValues
Application.CutCopyMode = False

ActiveSheet.Cells(1, 1).Select

End With

When the formula transfers to the destination sheet, all of the references in 'Units - Job Numbers' sheet become #REF!. Strangely enough, the original formula has the #REF! errors even though the formula was correct before executing the macro. Any suggestions?

Can you post your code here and what is the formula that you are getting inside cell? — ManishChristian, Sep 11 '15 at 14:35
I won't include my entire script, as it is quite lengthy and I am not having any issues apart from this line. I will include the formula after it is transferred from the origin sheet. — CJK, Sep 11 '15 at 15:17

Brian Pressler · Accepted Answer · 2015-09-11T16:18:38.790

1

This is my best guess as to what is happening... Are you possibly deleting cells in the 'Units - Job Numbers' sheet before or after you get to this line of code that copies the formula? If so, it could invalidate the references. Check your code for places where it deletes cells in 'Units - Job Numbers'. If you are deleting cells, set a break-point in your code immediately after that and check your original formula and you will see that the references turn to #REF then (in your Formulas and your Raw WIP Data sheet if it's after the copy).

edited Sep 11 '15 at 16:18

answered Sep 11 '15 at 15:50

Brian Pressler

6,653
2
19
40

Thank you for the answer, I realized this immediately after I edited my post and then tried to correct it. I am still having some issues with all of the value being indexed, but at least now I am not experiencing the `#REF!` error. – CJK Sep 11 '15 at 18:48

Array Formula Resulting in #REF Error

1 Answers1