I am trying to get the following long array into a vba sub procedure, however it continues to give the 'cannot set the formula array into the range class' error despite each of the pieces being less than 255 characters. Any help would be appreciated.
Thanks.
Please Login or Register to view this content.
Last edited by alansidman; 12-20-2013 at 10:32 AM. Reason: code tags added.
I'm not a SendKeys advocate, but I use it to insert long array formulas. Here's the sub and an example of usage:
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
BTW, you might build the whole formula as a string and see if it's correct. Your first Replace is going to replace two instances, and the second will replace none.
I dont think it will do all the work, but notice that after
you have no single occurence of "X_X_X())" because all that used to look like that after first repalce lookPlease Login or Register to view this content.
so first try toPlease Login or Register to view this content.
Next: I'd personally work other way - first prepare valid formula as a string and only after having valid formula ready, assign it to a cell. (may be it is worth reconsidering and reconstruct formula, or use some extra region to make partial calculations and make main formula shorter).Please Login or Register to view this content.
Last edited by Kaper; 12-19-2013 at 02:27 PM.
I ended up doing the following to get the long array entered, but when I went to copy and past the array down the column, I had to enter the copy and paste code into another sub. It works as long as I call them separately, but if I try to put them both in one sub or call one sub with the other, it copies down the exact formula without updating the row numbers and the cell that was copied is no longer an array. Any thoughts as to why this might be?
Please Login or Register to view this content.
''''^''''1
Last edited by shg; 12-20-2013 at 02:42 AM.
shg--I tried using the code you recommended, but to be honest, I did not understand a lot of what it was doing and could not get it to work in the way that it looks like it was intended which was to enter the array you defined as a constant into cell M1.
Thanks.
shg, I ended up using a lot of the code that you proposed and it worked in one instance, below:
However, when I tried the same thing with another formula, I receive a run time 1004 error message regarding the new constant, custrisk. I really appreciate all of the help so far and any insight here, would be a big help.Please Login or Register to view this content.
Thanks.
Please Login or Register to view this content.
Post the workbook?
Last edited by shg; 12-20-2013 at 02:34 PM.
I updated the insertarrayforumla function to incorporate everything you had in yours and it returns an error--argument not optional, so it seems to be looking for inputs to the other two portions of the function, the xlReferenceStyle and the Optional ByVal. I actually tried guessing some inputs for these and it crashed excel, so I am updating the function again. It seems reasonable that it is looking for the other two arguments, how does it work for you without the last two?
Thanks again.
Sorry, this line
should bePlease Login or Register to view this content.
The last argument specifies the reference style of the formula.Please Login or Register to view this content.
With that change, it worked fine.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks