Help with inserting an array formula through vba

[eAlchemist]

Please help! I can't figure out why this isn't working for me. Everytime I run this line, I get a "Run-time error '1004': Unable to set the FormulaArray property of the Range class". The formula works when I enter it by hand. What's up?

Thanks, Chris

Here is the line of code:
Worksheets(Sheet.Name).Range(formulaAddress).FormulaArray = formulaText

And the values for the items referenced:
?formulaAddress
D124
?Sheet.Name
GIRLS
?formulaText
IF(SUM(IF(RawData!$B$2:$B$14646=$A$1, IF(RawData!$C$2:$C$14646=$B118, IF(RawData!$D$2:$D$14646=$B124, IF(RawData!$E$2:$E$14646=$C124,IF(RawData!$F$2:$F$14646=D$1,RawData!$G$2:$G$14646,0),0),0),0),0))=0,D125,SUM(IF(RawData!$B$2:$B$14646=$A$1, IF(RawData!$C$2:$C$14646=$B118, IF(RawData!$D$2:$D$14646=$B124, IF(RawData!$E$2:$E$14646=$C124,IF(RawData!$F$2:$F$14646=D$1,RawData!$G$2:$G$14646,0),0),0),0),0)))

 

[VBAjedi]

Sheet.Name" is not a valid variable name, because you can't use periods in variable names. Try:

Worksheets(mySheet).Range(formulaAddress).FormulaArray = formulaText

Obviously you will need to change the variable name where you assign it a value, as well as anywhere else it appears in the code.

VBAjedi

 

[eAlchemist]

Thanks for replying...

then why does this work?
Worksheets(Sheet.Name).Range(formulaAddress).Formula = formulaText

Thanks,
Chris

 

[TonyJollans]

Hi phaedrus1990,

What was wrong with the answer xlbo gave you in thread707-846100? Have you tried converting the addresses to R1C1 style?

Jedi,

There is nothing wrong with Sheet.Name per se - a User TypeDef is an obvious example, or Sheet being an Object variable set to the active sheet.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[VBAjedi]

Tony,

I said that "Sheet.Name" is not a valid variable name (which it most definitely isn't). It was a case of the right answer to a question not being asked. [LOL]

Anyway - moot point since you spotted the double-posting.

VBAjedi

 

[TonyJollans]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[eAlchemist]

Sorry for the double-post. I was worried that since the post above seemed to indicate a resolution, people would not go on to read mine (I hadn't gotten any responses in a while).

I think that I've found the problem- there is a problem in excel that doesn't allow you to pass an arrayformula longer than 255 characters:

http://support.microsoft.com/default.aspx?scid=kb;en-us;201354

So... I'm trying to work around by putting the formula into Formula and then using "SendKeys" to F2 and then Ctrl + Shift + Enter to change it to an array formula, but it doesn't seem to be working.

Any ideas?

Thanks,
Chris

 

[eAlchemist]

OK, I've made a little more progress, but I'm still having a problem. When I run the code below (run it, not step through it), the SendKeys("{F2}") acts on the VBA editor, not on Excel. How do I fix this?

Thanks,
Chris

 

[TonyJollans]

Hi phaedrus1990,

M$ are not very forthcoming with any information about this, are they?

Could you shorten your formula by defining some names? It's a bit too much effort for me to set up test data for this, but this might be worth a try ..

[blue]With ActiveWorkbook.Names
    .Add Name:="RDB", RefersToR1C1:="=RawData!R2C2:R14646C2"
    .Add Name:="RDC", RefersToR1C1:="=RawData!R2C3:R14646C3"
    .Add Name:="RDD", RefersToR1C1:="=RawData!R2C4:R14646C4"
    .Add Name:="RDE", RefersToR1C1:="=RawData!R2C5:R14646C5"
    .Add Name:="RDF", RefersToR1C1:="=RawData!R2C6:R14646C6"
    .Add Name:="RDG", RefersToR1C1:="=RawData!R2C7:R14646C7"
End With

[D124].FormulaArray = "=IF(SUM(IF(RDB=$A$1, IF(RDC=$B118, IF(RDD=$B124, IF(RDE=$C124,IF(RDF=D$1,RDG,0),0),0),0),0))=0,D125,SUM(IF(RDB=$A$1, IF(RDC=$B118, IF(RDD=$B124, IF(RDE=$C124,IF(RDF=D$1,RDG,0),0),0),0),0)))"
[/blue]

(note I haven't switched your references to R1C1 and may have made an error in my editing of your text as I haven't tested this)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[eAlchemist]

My problem is no longer with the FormulaArray property. I've given up on doing that way directly because of the Microsoft note on the Excel bug- Do you guys think that using R1C1 is a work-around?

I need help with the SendKeys command. How do I make sure that the SendKeys command goes directly to Excel?

Thanks,
Chris

 

[xlbo]

please think VERY carefully before using sendkeys - it is a notoriously unreliable way of coding and there is almost always a better way. One that I can think of straight off the bat is holding the formula in a blank cell somewhere and simply copy/ pasting it into position - that way you don't have to worry about formula string length or arrays or anything....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[eAlchemist]

Thank you! You had a great idea. I had already written the array formula to the Formula property of another cell. This code was writing it back. By setting the FormulaArray property in the destination cell equal to the Array property in the origination cell, I was able to work around the Excel bug.

Thanks!