Why do I get this VBA error message?

[spydey]

I have a table ("Fruit") in sheet "Fruit" which is comprised of the header (row 1) and then a single row in the body.

The columns are:

Apples
Bananas
Total Fruit

I have no data in row 1, it is simply blank.

If I input the following code into a vba module, it works fine.

Sub Fruit()

Dim tblFruit As ListObject
Dim wb As Workbook
Dim ws As Worksheet


Set wb = ThisWorkbook
Set ws = wb.Worksheets("Fruit")
Set tblFruit = ws.ListObjects("Fruit")


tblFruit.ListColumns("Total Fruit").DataBodyRange.FormulaArray = "=Array Formula Here"


End Sub

The formula is placed in-cell in column Fruit Total.

The formula is an array formula that normally I would input in-cell via CSE.

If I add additional columns after running the VBA, the formula auto-fills down to each new row, which is what I want.

The issue I am having is if I have more than one row in my table body before running the code, when I run the code I get an error message.

It states that it is unable to set the the ArrayFromula property of the range class.

Why does it work when there is a single row in the body of the table, but when there are >1, it errors?

How can I go about fixing this?

Thanks in advance for your time and assistance!

-Spydey

 

[Fluff]

Try

tblFruit.ListColumns("Total fruit").DataBodyRange.Rows(1).FormulaArray

 

[spydey]

Try

tblFruit.ListColumns("Total fruit").DataBodyRange.Rows(1).FormulaArray

Hey there Fluff!! Thanks for the response. I will give that a try here in a moment.

-Spydey

P.S. Just tried it and worked great!! Thank you for your assistance!