1

Hi I'm trying to insert a formula array this way 

Dim F1 As String
Dim f2 As String

F1 = "=SUM(--(FREQUENCY(IF(rptEURegThresholds!C[7]=""Yes"",IF(rptEURegThresholds!C[16]=""No"",IF(rptEURegThresholds!C[15]=""Total Disclosure"",1,1"
f2 = "MATCH(rptEURegThresholds!C[-1],rptEURegThresholds!C[-1],0)))),ROW(rptEURegThresholds!C[-1])-ROW(rptEURegThresholds!R[-6]C[-1])+1)>0))"

With sh8.Range("F7")
    .FormulaArray = F1
    .Replace "1,1", f2, xlPart
End With

But something is wrong and can't find the issue. Please help!!!

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Nicole Velasco
  • 11
  • 2
  • Does it give you an error message? if so, what is the message and line it says there is an error on? – KySoto Oct 09 '18 at 16:17

1 Answers1

0

I could think of 2 problems in the formula

  1. Total no of characters in your formula ( including both F1 & F2) is around 272, which exceeds 255 characters limit of formulaArray in Excel Kindly refer SO post Overcoming the 255 char. limit for formulaArray in Excel VBA.

  2. The code is bound to halt at the line

    .FormulaArray = F1

    with error “unable to set the FormualArray property of the range class” As formula is incomplete at this stage (mismatches in parenthesis etc).

So I tried with shortening the Worksheet name from “rptEURegThresholds” to “rptEURT” and combining both F1 & F2 strings as F1. It worked without any error. However correct working of the formula may please only be checked by you.

Dim F1 As String

 F1 = "=SUM(--(FREQUENCY(IF(rptEURT!C[7]=""Yes"",IF(rptEURT!C[16]=""No"",IF(rptEURT!C[15]=""Total Disclosure""," & _
    "MATCH(rptEURT!C[-1],rptEURT!C[-1],0)))),ROW(rptEURT!C[-1])-ROW(rptEURT!R[-6]C[-1])+1)>0))"


    With Sheets(3).Range("F7")
        .FormulaArray = F1
    '    .Replace "1,1", f2, xlPart
    End With
Ahmed AU
  • 2,757
  • 2
  • 6
  • 15