Create New row in a table and copy/update formulas VBA
Hi, I'm new to VBA
I've got my excel table that uses certain formulas to calculate values for some cells.
I use UserForm to fill in that table however when new row with input from the form gets added to the table it does not inherit updated formulas from the row above. Therefore no calculations are taking place in that row. I use the following code to insert a new row with UserForm data:
Any help would be greatly apreciated. Thanks
Please Login or Register to view this content.
Last edited by whitenoise22; 07-22-2012 at 05:05 PM.
Bump
Attach please a sample of your file for checking
I'd like to but I've got confidential data in there. Sorry.
OK I stripped down the file from all other sheets and confidential data
I tested your file, for me it works, calculations are taking.
Maybe some macro disables the calculations
Thank you for your help so far. That's true it is working in that file indeed. I've missed that because I was working with later version. I think problem might have something to do with other functions that I've added later. Please see the attached file.
Many Thanks!
In the sheet Data I don't see formulas in the blank cells, why ?
I get it. Thank you. I thought it will automatically copy the formula when creating a new row so I don't have to copy it down manually. Is there a way to achieve this without stretching down formula copy frame in each column ?
Also the other problem is that I've got garages listed in Garages sheet and this is fed to ComboBox1 where two columns from 'Garages' are displayed A&B; However when the selection is made in fillupEntry form only the value from column 'B' is passed to the cell in 'Data' column 'N'.
Any chance you could also help me on this? I need that selection from the comboBox is passed to 'N' column in 'Data' as for instance 'A2 & B2' (Tesco Bangor)
Last edited by whitenoise22; 07-23-2012 at 05:51 PM.
I don't understand how, explain it me. In the test sheet the formula were present in the blank cells and these were accessible.Originally Posted by whitenoise22
I'm not a guru
Why does your profile show Excel 2003, but you add a 2007/2010 workbook?
Please Login or Register to view this content.
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Because it was hanging the page when I tried. Now it is OK
Thank you for your help Roy. I've used the code but it seems to add two rows at the time. One empty (row '11') and one correct below. Therefore formulas are not working as they are related to the data in the row above.
Also in C11 suddenly I get this formula
and I don't know where it is from. At the same time other cells in row '11' have no formula copied over.Please Login or Register to view this content.
Could you please point me to where I get it wrong? Thanks
Tom
This seems to be working
Please Login or Register to view this content.
Thanks. I will check this
---------- Post added at 08:07 AM ---------- Previous post was at 07:41 AM ----------
Thanks! Now it's not adding an extra row.
But still It is not copying down formulas from (only) col D and G in 'Data' other formulas however are copied down to the newly created row.
This is the code I currently use:
Please Login or Register to view this content.
Checking the table manually not all the formulas copy. I suggest you try reformatting it as a Table, it doesn't seem to be picking up some formulas
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks