Insert formula in range of cells through VBA Code

[hrayani]

Hello Firends,

I am trying to insert the formula in the range of cells through a VBA code as below but it says that "Unable to set the formula array property of the range class" for the second formula. The first one works just fine. Can anyone please check & let me know what have I done wrong

This one works

ws.Range("AC2").FormulaArray = "=IFERROR(INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),"""")"
ws.Range("AC2:AC2000").FillDown

But this does not

ws.Range("AC2").FormulaArray = "=IFERROR(IF(AB2=""Not Applicable"",""Not Applicable"",IF(INDEX(fri_dpi_labtest_quantity,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 1""=fri_dpi_labtest_category),0))<L2*98%,INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),""Not Applicable"")),"""")"
ws.Range("AC2:AC2000").FillDown

Regards,

Humayun

 

[Alex Blakenburg]

Can you try using Formula2 instead of FormulaArray ?

 

[hrayani]

Can you try using Formula2 instead of FormulaArray ?

Hi Alex,
I did not understand your point here... What do you mean by Formula2 ??

 

[Alex Blakenburg]

See the blue below:
I assume you only need the 2nd one.
Since you have MS365 Formula2 will enter an array formula.

ws.Range("AC2").Formula2 = "=IFERROR(INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),"""")"
ws.Range("AC2:AC2000").FillDown


ws.Range("AC2").Formula2  = "=IFERROR(IF(AB2=""Not Applicable"",""Not Applicable"",IF(INDEX(fri_dpi_labtest_quantity,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 1""=fri_dpi_labtest_category),0))<L2*98%,INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),""Not Applicable"")),"""")"
ws.Range("AC2:AC2000").FillDown

 

[RoryA]

Your array formula is over 255 characters, which is why it doesn't work. Since you appear to be using 2016, Formula2 won't help you.

You might be able to get away with using:

ws.Range("AC2").FormulaArray = "=IFERROR(IF(AB2=""NA"",""NA"",IF(INDEX(fri_dpi_labtest_quantity,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 1""=fri_dpi_labtest_category),0))<L2*98%,INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),""NA"")),"""")"
With ws.Range("AC2:AC2000")
   .FillDown
   .Replace "NA", "Not Applicable", xlpart
end with

 

[hrayani]

Your array formula is over 255 characters, which is why it doesn't work. Since you appear to be using 2016, Formula2 won't help you.

You might be able to get away with using:

ws.Range("AC2").FormulaArray = "=IFERROR(IF(AB2=""NA"",""NA"",IF(INDEX(fri_dpi_labtest_quantity,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 1""=fri_dpi_labtest_category),0))<L2*98%,INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),""NA"")),"""")"
With ws.Range("AC2:AC2000")
   .FillDown
   .Replace "NA", "Not Applicable", xlpart
end with

Hi Rory,

Thanks for the reply -

After replacing Not Applicable to NA, the formula still contains over 260 characters - therefore, I had to remove the iferror to get it down to under 255 characters
Now it is working just fine

Now that I know what was causing it not to work - I can & will play with a bit if I have to have the iferror in any case - like I can rename the named ranges with short names etc or using some helper columns

Regards,

Humayun

 

[hrayani]

See the blue below:
I assume you only need the 2nd one.
Since you have MS365 Formula2 will enter an array formula.

ws.Range("AC2").Formula2 = "=IFERROR(INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),"""")"
ws.Range("AC2:AC2000").FillDown


ws.Range("AC2").Formula2  = "=IFERROR(IF(AB2=""Not Applicable"",""Not Applicable"",IF(INDEX(fri_dpi_labtest_quantity,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 1""=fri_dpi_labtest_category),0))<L2*98%,INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),""Not Applicable"")),"""")"
ws.Range("AC2:AC2000").FillDown

Hi Alex,
I don't have 365 version as @RoryA pointed out
but thanks anyways for taking your time
Regards,
humayun

 

[Alex Blakenburg]

No problem. I thought I checked your version but somehow I got it wrong. Glad Rory was able to sort it out.

 

[RoryA]

After replacing Not Applicable to NA, the formula still contains over 260 characters

Weird - I make it 248.

 

[hrayani]

Book1
	A
1	=IFERROR(IF(AB2=""NA"",""NA"",IF(INDEX(fri_dpi_labtest_quantity,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 1""=fri_dpi_labtest_category),0))<L2*98%,INDEX(fri_dpi_labtest_date,MATCH(1,($A2=fri_dpi_labtest_po)*(""FRI 2""=fri_dpi_labtest_category),0)),""NA"")),"""")
2	261
Sheet1

Cell Formulas
Range	Formula
A2	A2	=LEN(A1)