FormulaArray produces error 1004 and error 424

1
votes

When trying to use an array formula I was getting:

Error 1004: "Unable to set the formulaarray property of the range class"

Research indicated that it was because my formula was too long, so I tried a "Replace" trick described by others.

I now get:

Runtime error '424': "Object required"

on my first .Replace line.

For k = LastRow + 4 To LastRow + 3 + KeyListIndex
    FormulaArrayString = "=MIN(IF('" & KeyDataRange & "='" & DataFindingsTab & "'!$A" & CStr(k) & ",'" & DateDataRange & "))"
    With Sheets(DataFindingsTab).Cells(k, 2).FormulaArray = "=MIN(IF('KeyDataRange='" & DataFindingsTab & "'!$A" & CStr(k) & ",'DateDataRange))"
        .Replace "KeyDataRange", TabName & " '!$" & Number2Letter(KeyCol) & "$2:$" & Number2Letter(KeyCol) & "$" & CStr(LastDetailRow)
        .Replace "DateDataRange", TabName & " '!$" & Number2Letter(DateCol) & "$2:$" & Number2Letter(DateCol) & "$" & CStr(LastDetailRow)
    End With
Next k

Variable FormulaArrayString is in there to check my work, and watching it confirms that it matches the working array formula lin my worksheet.

Update: I've fixed the newline issue and also a few errant spaces I noticed, so now my code looks like this but I'm still getting the error:

    For k = LastRow + 4 To LastRow + 3 + KeyListIndex
      FormulaArrayString = "=MIN(IF('" & KeyDataRange & "='" & DataFindingsTab & "'!$A" & CStr(k) & ",'" & DateDataRange & "))"
      With Sheets(DataFindingsTab).Cells(k, 2)
        .FormulaArray = "=MIN(IF('KeyDataRange='" & DataFindingsTab & "'!$A" & CStr(k) & ",'DateDataRange))"
        .Replace "KeyDataRange", TabName & "'!$" & Number2Letter(KeyCol) & "$2:$" & Number2Letter(KeyCol) & "$" & CStr(LastDetailRow)
        .Replace "DateDataRange", TabName & "'!$" & Number2Letter(DateCol) & "$2:$" & Number2Letter(DateCol) & "$" & CStr(LastDetailRow)
      End With
    Next k

Is there a way to directly watch what's being passed to .FormulaArray so I can make certain it is what it is supposed to be?

The formula I'm aiming for is:

=MIN(IF('1| Labor'!$B$2:$B$585='BOE Post-DoD Data Findings'!$A14,'1| Labor'!$E$2:$E$585))

2
excelvba

2 Answers

0
votes

You need a newline before the .FormulaArray

For k = LastRow + 4 To LastRow + 3 + KeyListIndex
  FormulaArrayString = "=MIN(IF('" & KeyDataRange & "='" & DataFindingsTab & "'!$A" & CStr(k) & ",'" & DateDataRange & "))"
  With Sheets(DataFindingsTab).Cells(k, 2)
    .FormulaArray = "=MIN(IF('KeyDataRange='" & DataFindingsTab & "'!$A" & CStr(k) & ",'DateDataRange))"
    .Replace "KeyDataRange", TabName & " '!$" & Number2Letter(KeyCol) & "$2:$" & Number2Letter(KeyCol) & "$" & CStr(LastDetailRow)
    .Replace "DateDataRange", TabName & " '!$" & Number2Letter(DateCol) & "$2:$" & Number2Letter(DateCol) & "$" & CStr(LastDetailRow)
  End With
Next k

...but your formula looks off on that line (missing an ending ' ?)

0
votes

I solved it!

I had forgotten something I read when about using .Replace to shorten array formulas--the shortened version with your placeholders still needs to be syntactically correct. Tim Williams' question about the closing ' actually was part of the issue--even though everything looked OK once the .Replaces were done, Excel didn't like that what I was putting into .ArrayFormula wasn't syntactically correct.

Here's the code that works:

    For k = LastRow + 4 To LastRow + 3 + KeyListIndex
      With Sheets(DataFindingsTab).Cells(k, 2)
        .FormulaArray = "=MIN(IF(KeyDataRange='" & DataFindingsTab & "'!$A" & CStr(k) & ",DateDataRange))"
        .Replace "KeyDataRange", "'" & TabName & "'!$" & Number2Letter(KeyCol) & "$2:$" & Number2Letter(KeyCol) & "$" & CStr(LastDetailRow)
        .Replace "DateDataRange", "'" & TabName & "'!$" & Number2Letter(DateCol) & "$2:$" & Number2Letter(DateCol) & "$" & CStr(LastDetailRow)
      End With
    Next k