Can an array formula be included in VBA code?

**Aland2929** · 04-09-2013, 04:20 AM

I want to include the following array formula in my code but get an object defined error?

Range("AV3:AV" & SecondLastRow - 1) = "={SUM(COUNTIFS(J:J,AT3,C:C,ExcludeCT))}"

Thanks in advance

**AndyLitch** · 04-09-2013, 04:30 AM

Range("AV3:AV" & SecondLastRow - 1).FormulaArray = "=SUM(COUNTIFS(J:J,AT3,C:C,ExcludeCT))"

**Aland2929** · 04-09-2013, 04:37 AM

Thanks Andy but the AT3 part of the formula remains absolute and it needs to be relative?

**AndyLitch** · 04-09-2013, 04:51 AM

The only thing i can think of then is to write the formula to AV3 and paste it down the column ??

**Aland2929** · 04-09-2013, 02:58 PM

This is the code I am trying to use but is not working?

Range("AV3:AV3").Copy Range("AV3:AV & SecondLastRow - 1)

Thanks in advance

**Aland2929** · 04-09-2013, 03:02 PM

I am getting an error on the 2nd row??

Range("AV3:AV3").FormulaArray = "=SUM(COUNTIFS(J:J,AT3,C:C,ExcludeCT))"
Range("AV3:AV3").Copy Range("AV3:AV & SecondLastRow - 1")

**Aland2929** · 04-09-2013, 03:12 PM

The error message is :Method range of Object Global failed

Thanks in advance

**Aland2929** · 04-10-2013, 12:10 AM

I think the problem is with the Paste part of the code but I can't figure it out? Would appreciate some help.
Thanks in advance

**AndyLitch** · 04-10-2013, 01:23 AM

Try this

Range("AV3").FormulaArray = "=SUM(COUNTIFS(J:J,AT3,C:C,ExcludeCT))"
    Range("AV3").Select
    Selection.Copy
    Range("AV3:AV" & SecondLastRow - 1).Paste

**Aland2929** · 04-10-2013, 02:06 AM

Thanks for your response Andy. I get the following error message relating to the last line of code:

[Object does not support this property or method]

Thanks in advance

**Aland2929** · 04-14-2013, 07:54 AM

I would appreciate any help with this as I have not been able to resolve it.

Thanks in advance

Can an array formula be included in VBA code?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Re: Can an array formula be included in VBA code?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions