Hello. What I'm trying to do is copy a range into an array, including any formulas that cells in the range may contain, and then insert that range into new rows. Basically, making a copy of one range and pasting it into another, with all formulas being copied also.This works very well, but formulas in the cells are not getting copied, only their value gets copied (not the actual formula). Please help
Please Login or Register to view this content.
I'm afraid you will only get the values in the array with that code.
You can get the formulas and value like this but I don't know how it will work the other way.
Can't you just do a straight copy from range to range?Please Login or Register to view this content.
Last edited by Norie; 11-27-2012 at 09:48 PM.
If posting code please use code tags, see here.
Thanks for the reply Norie. I get "Run-time error 1004. Application-defined or object-defined error" with the above code. I had tried that already. I can't do a straight range copy because I run some other code to filter the array elements before the copy. Using an array is needed because the copies could be 1000's of rows, much too slow without the array.
Last edited by jimmalk; 11-27-2012 at 10:01 PM.
Where do you get that error?
Also where is tbNumRows declared and given a value?
tbNumRows is a field on the form I use to define the numbers of copies I want to make. With the above code, I get the error when I open the form. Without the ".formula" attached all works great, with the exception of the formula values getting copied (when I actually want the formula to get copied). The copy code is in UserForm_Initialize(), so its before I even use tbNumRows.
By the way, this part of the code doesnt give any errorBut this does give an errorPlease Login or Register to view this content.I've been looking at it everyway, but cant figure it out. I imagine I'm using the wrong syntax??Please Login or Register to view this content.
What do you have in the range you are populating RefToCopy?
Also which worksheet is it on?
I only tried it with simple formulas, a few values and no errors.
The range has numbers, strings, formulas. I copy the range into the array, and on my form I have check boxes to choose which cells I want to be copied. If I dont want it copied I put "" into that element (thereby changing it to a blank cell). tbNumRows is how many copies to paste back to the worksheet. All works perfectly except in the UserForm_Initialize(), wherefails. Its the first line of code. Taking out ".formula" works though (but doesnt copy formulas)Please Login or Register to view this content.
Can you attach a sample workbook?
It would be difficult Norie, the file is 70MB, and I dont know where to start to delete stuff and still leave the form functioning. The form has ties to many pages. All is working perfectly though, except for this formula copy detail. The code I showed above is independant of the other code
Last edited by jimmalk; 11-27-2012 at 11:10 PM.
Can you not make a workbook with just the form and data this code is associated with?
I'll try. Plz give me 20 minutes or so. Thx
Ok Norie, I made up an example workbook showing the problem. Thx for waiting and your helpThe sheet protection and manual calculate is how my worksheet is set up.
Seems to work fine for me.
Is there anything specific I should do to reproduce the problem?
I've tried with the cursor in the table and outside the table.
I also tried it with other sheets active.
Column I copies down as text, not as a formula. Column L copies down the formula, but it changes the cell references. I need for the formula to be copied down exactly as they were in the original cell. In this sample file I'm not getting the same error I got before though. dont know what's up there. But the same, the formulas arent copying down correctly.
Ahhh!! I changed columns with formulas to number format "General", and changed the formula to "=$K$2" and "=$M$2*3-100". Works now. But only problem is there is that one of the columns with formula has a custom format for that column. Guess I need to switch the column to "General" before the copy, and then to change it back to the custom format after the paste?
Try this, it seems to work.
Oh, and you'll need to format column I as General or you'lll end up with the formulas displayed.Please Login or Register to view this content.
I had earlier tried using a for-next loop to do this but it was too slow when doing 1000's of new rows at one time. But the problem seems to be that the columns with formulas have to be formatted as "General" when the paste is made, otherwise the cell treats the paste as text. I might still have another issue with this from the other error message I was getting, but I'm going to mark this as solved because the column formatting seems to be the principle problem. I've rep'ed you for your kind help. Thanks much Norie.
Last edited by jimmalk; 11-28-2012 at 01:10 AM.
I was going to suggest changing the references in the formulas to absolute but thought you wouldn't want to change them.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks