Hi,
Would someone help me?
I need to enter this formula below in a group of cells with a script Im using, However I need to enter Ctrl Shift Enter to make it work correctly.
Thank You, MikePlease Login or Register to view this content.
Hi,
Would someone help me?
I need to enter this formula below in a group of cells with a script Im using, However I need to enter Ctrl Shift Enter to make it work correctly.
Thank You, MikePlease Login or Register to view this content.
Last edited by realniceguy5000; 08-25-2011 at 04:19 PM.
hi, Mike, instead of FormulaR1C1 try FormulaArray
VB help:
FormulaArray Property
Returns or sets the array formula of a range. Returns (or can be set to) a single formula or a Visual Basic array. If the specified range doesn't contain an array formula, this property returns Null. Read/write Variant.
Remarks
If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style (see the second example).
Example
This example enters the number 3 as an array constant in cells A1:C5 on Sheet1.
This example enters the array formula =SUM(R1C1:R3C3) in cells E1:E3 on Sheet1.Please Login or Register to view this content.
Please Login or Register to view this content.
Note that the information in the helpfile is incorrect in that A1 notation is acceptable.
Thank You,
Is this what you mean?
I tried this:
I also tried this:Please Login or Register to view this content.
.Please Login or Register to view this content.
However I get an error:"Unable to set the FormulaArray property of the range class"
When I enter this manually into the cell and hit ctrl shift enter it works, I also tried to record a macro but get an error unable to record.
Any Idea's? Mike
Last edited by realniceguy5000; 08-25-2011 at 02:44 PM.
It's because the maximum length of the formula string is 255 characters. Also, quotes within the string need to be doubled up.
You can work around the limitation by taking a common part of the formula, substituting in a short string, putting it into the cell and then replacing common part:
For a comprehensive overview of Range.FormulaArray, see here.Please Login or Register to view this content.
I think I found why it wont work,
Ok Looks like my formula is longer than 255 or 256 Characters. I found this information here
http://www.dailydoseofexcel.com/arch...rmulas-in-vba/
I'm going to try and get this to work with my formula and I'll post back the results I get. However if someone clever can attempt it as well it would be great as i'm sure I'll have problems. lol
Thank You, Mike
Here's a function I use for inserting large array formulas:
For example,Please Login or Register to view this content.
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Ok, Late as always.
Colin, I tried your script as follows. It places the formula in the cell with the "xxx" However the replace command wont work. So it wont replace the xxx with the rest of the formula.
I must have missed something along the way?Please Login or Register to view this content.
Can you advise: Thank You, Mike
Ah, I expect that's because in the interface you have it set to A1, not R1C1 notation. Change the replace string to the A1 equivalent:
Please Login or Register to view this content.
It now works as expected.
That is something I would not have thought of for some time.
Anyway, Thanks to all for the input...
Mike
I know this is an old post, but I hate to create yet another thread for this topic if you can stlll help me out. My formulas were working great in this macro loaded worbook I have but all of a sudden now that the linked tables are refreshed, occasionally I end up with #VALUE error. As such, I've now been looking for a way to "remind" it that it's an array formula without stopping the code, manually hitting ctrl+shift+enter, then continuing the code.
Now, I played around with this until it worked, but with one exception. One of the cells in the range ends up with an apostrophe preceding the formula and I cannot figure out why. The formulas below are an example of what the code is working with. Red is the one failing, the other is the same formula with the exception of the table column it's looking at - and it works. It's not that it's a bad formula either. Once the apostrophe is removed it works again.
Formula:Please Login or Register to view this content.
Please Login or Register to view this content.
ugogirl,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
So I'm supposed to have others figure out why code written by someone else sometimes works and sometimes doesn't?
Ok then. I didn't realize it was "hijacking" a thread to ask this. Not worth posting a new thread to me at this time since I tried something else instead.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks