• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array Formula Error in excel 2007 VBA

C

cagirish81

New Member
Hi,


I want to define an array formula for range of cells in excel 2007 vba. I am using following vba code but getting error : Unable to set the FormulaArray property of the range class. Plz help..


Sub ArryForm ()


Dim IncFormula As String


IncFormula = "=IFERROR(-INDEX('Data Summary Loc-Wise'!R3C1:R1000C1000, MATCH(R3C1 & R2C1 & R5C,'Data Summary Loc-Wise'!R3C1:R1000C1 & 'Data Summary Loc-Wise'!R3C2:R1000C2 & 'Data Summary Loc-Wise'!R3C4:R1000C4,0),MATCH(RC1,'Data Summary Loc-Wise'!R3C1:R3C1000,0))/10^3,0)"


Range(Cells(7, 2), Cells(10, C + 1)).FormulaArray = IncFormula


End Sub


Thanks in advance..
 
Sincere apology for inconvenice.. Forgot to mention one line of vba code in below post


C = Application.WorksheetFunction.CountIfs(Sheets("Sheet2").Range("A:A"), "Auto", Sheets("Sheet2").Range("B:B"), "May-12")
 
Hi Girish ,


The Excel help says this :

[pre]
Code: 
The FormulaArray property also has a character limit of 255.
[/pre]
Your string length is 259. Can this be the reason ? Remove the IFERROR wrapper and try.


Narayan
 
Hi, cagirish81!

You can also try changing cells notation from R1C1 to A1, that's to say replace this:

R3C1:R1000C1000, R3C1, R2C1, ...

by:

A3:ALL1000, A3, A2, ...

and so on.

Surely you could crop the damned 4 characters is excess...

Regards!
 
Hi JB-007,


I am not able to go beyond this line..

Code: 
C = Application.WorksheetFunction.CountIfs(Sheets("Sheet2").Range("A:A"), "Auto", Sheets("Sheet2").Range("B:B"), "May-12")


C is always shows 'Zero' if "May-12" is not in TEXT format.

Can you please only solve the C Value for me.. how can I check Month(B:B) = "May-12" in [b].CountIfs[/b] worksheetfunction in VBScript..

but the same formula in EXCEL working fine.. :(


@cagirish81,

Hi cagirish81

* Is this not possible to set the [code]Range("A100:A100")
coz, its looks huge for me.. But please set all range parallel.. (B100:B100) or (C100:C100) :)

* why there is a Single (-) at Index Function.. double minus is fair..(--)

* Is this not possible to set FormulaR1C1 instead of FormulaArray.. as I hope both gives you same result.. We use Ctrl+Shift+Enter when we need run in loop in the Cells..

and when you have already choose VBA, then why you are giving extra effort to GridLine Sheet..

Range(Cells(7, 2), Cells(10, c + 1)).FormulaR1C1 = IncFormula[/code]

* with FormulaR1C1 everything working fine for me.. as I dont have to verify the correct Result.. :)

Finally just a normal request..

Considering uploading a file...


Regards,

Deb
 
@Debraj Roy

Hi!

Even I don't like too much the solution, try this while I go on playing around a little more:

-----

[pre]
Code: 
Sub x()
Dim C As Integer, A As String, B As String
A = ">=" & CLng(DateSerial(2012, 5, 1))
B = "<=" & CLng(DateSerial(2012, 5, 31))
C = Application.WorksheetFunction.CountIfs(Sheets("Sheet2").Range("A:A"), "Auto", _
Sheets("Sheet2").Range("B:B"), A, _
Sheets("Sheet2").Range("B:B"), B)
Debug.Print C
End Sub
[/pre]
-----


Regards!
 
@Debraj Roy

Hi!

Range criteria appears to not like dates neither in dd/mm/yyyy nor in mm/dd/yyyy, so go on with the equivalent number.

You're welcome.

Regards!
 
Hi ,


Again , according to the Excel help :

[pre]
Code: 
If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style.
[/pre]
Narayan
 
Hi, cagirish81!

Haven't ever actually written such a formula from VBA I lightly passed over the rules, that I didn't knew. I apologize for my fast and easy suggestion.

Regards!


@NARAYANK991

Hi!

Thanks for the correction, I learned something new today.

Regards!


@SirJB7

Hi!

If I could put my hands on the Redmond guy who implemented this feature...

Regards!
 
Did you try Macro Recorder to record formula you are implementing?


@Narayank:

The Excel Help Reference doesn't seem to be correct. I tried to implement following formula to verify and it works so SIRJB7's suggestion might work out:


Code: 
Range("C3").FormulaArray = "=SUM(IF(D2:D10=1,2,0))"
 
@shrivallabha

Hi!

Take it easy... just now I'm finishing to learn that new particularity of the FormulaArray property... and should I begin to unlearn it? That's not fair...

Regards!

PS: I think you're a relative of that Redmond guy who wants to save him :)
 
@SirJB7,


I was little curious as there is:

.Formula

.FormulaR1C1

for normal formulas but only .FormulaArray and no .FormulaArrayR1C1 so tried it out of curiosity (the formula mentioned above) and it worked. Hence my post.


If you will try following, they both will work and will give identical result:

[pre]
Code: 
Range("A1").FormulaArray = "=SUM(IF(R1C2:R10C2=1,2,0))"
Range("A1").FormulaArray = "=SUM(IF($B$1:$B$10=1,2,0))"
[/pre]
On a lighter note: Where on earth this Redmond is? I can't help myself most of the times so playing savior is way beyond me and then to save a man whose face I don't know. Go figure how hard will it be.


And on even more lighter note, if we were to rely on implementing the formulas as "Redmond" demonstrated then where on earth would we be?


@Narayank and other fantastic guys: I admire your contributions.
 
Hi Redmond Haters..


* Just to inform you that, yes.. Its true, we cant use A1 style in .FormulaArray in < Excel 2007..

check the last comment by Brad Yundt

http://msdn.microsoft.com/en-us/library/bb208529.aspx


* by separating the text in two variable.. we can also override >255 rule..

http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/


* If we change EXCEL to R1C1 style, then run both below line.. Excel automatically converts the both to R1C1 Style.. and vice-versa.. if we uncheck again R1C1 style, both automatically converts to A1 Style.. so converting the Formula in Gridview Excel is just the feature of Excel not VBA's..

[pre]
Code: 
Range("A1").FormulaArray = "=SUM(IF(R1C2:R10C2=1,2,0))"
Range("B1").FormulaArray = "=SUM(IF($B$1:$B$10=1,2,0))"
[/pre]
* Don't hesitate to shout on me, if any of my R&D are not acceptable, as all of you are Virtual Teacher for me and I admire a lot.


Regrads,

Deb
 
@shrivallabha

Hi!

You're right, it works, at least in 2007/2010, couldn't check in 2003. I'd unlearn my recent adquisitions.

Regards!

PS: http://en.wikipedia.org/wiki/Redmond,_Washington


@Debraj Roy

Hi!

I should marry someone at Redmond, my feelings go from love to hate, it's like a marriage.

Regards!

PS: Better a girl, just in case :p
 
Hi Deb,


Thanks for the links. I will be the first one to *shout*


If you'd have closely read Brad Yundt's comment (on 8/21/2008 so 2007 was the latest version then I'd suppose)


His comment as it is:

Code: 
The FormulaArray property will accept formulas written in A1 referencing style even though the documentation claims otherwise. This is true for Excel 97 through 2007.


So it rather means you can use A1 styled references in FormulaArray through all versions of Excel starting from 1997.
 
@SirJB7

Hi, myself!

So long...

Reminder for target change:

- old target, implementer survives

- new target, what's that red point in the middle of the documenter's head?

Regards!
 
Dear All,


Thnx for suggestions..


@ Narayan


the code is perfectly working after reducing the formula length to 255 characters. Many thanx for your help.


@ Debraj Roy


While calculating value of C "May-12" should be in date format ie MMM/YY. Regret for not mentioning that.


Secondly, in index formula there is a negetive (-) sign as I want to arrive at negetive values.


Once again thnx a ton to all of you.


Have a nice day..
 
You must log in or register to reply here.
Back
Top