hi, need help with this file that I create a macro but have some problems due to the fact that every day new information is added to the file, so the number of rows keeps increasing every day.
how can I add a formula that can calculate from the first row to the last?
Last edited by MacExcel23; 09-29-2023 at 01:41 PM.
So what is to be calculated?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
hi, the calculation is just subtotal of each of 6 different columns. what I having trouble is to have the calculation exactly at the end of the last row due that this files keeps updating every day therefore adding more rows.
hi, the calculation is just subtotal of each of 6 different columns. what I having trouble is to have the calculation exactly at the end of the last row due that this files keeps updating every day therefore adding more rows.Originally Posted by JohnTopley
Please Login or Register to view this content.
Re: Hello, need help creating a macro with calculation, increasing rows every day
Hi,
just using Sub Total Excel feature, thus without any VBA procedure …
Thanks, but it wont work as my file gets updated every day so I will always find more rows added.
thanks a lot, I will give it a try in my macroPlease Login or Register to view this content.
Re: Hello, need help creating a macro with calculation, increasing rows every day
Wrong ! As it's a well designed & well known Excel feature …
Worksheet evet is used. The sums for E and F column is available in N2 and O2 cells.
addition of rows will be taken care of by worksheet event.
Code:
How to use workheet event the codePlease Login or Register to view this content.
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Or simply so (See attached)
Experience trumps academics every day of the week and twice on Sunday.
Further to attachment in Post #11.
If you want the formulas in E2 and F2 to be dynamic, you could change them to
In E2: =SUM(OFFSET($E$3, 0, 0, COUNTA($E:$E), 1))
In F2: =SUM(OFFSET($F$3, 0, 0, COUNTA($F:$F), 1))
Put your cursor in A3 and click on "Freeze Panes" and scroll as much as you like. The totals will always be visible.
thanks a lot
thanks so much for your helpWorksheet evet is used. The sums for E and F column is available in N2 and O2 cells.
addition of rows will be taken care of by worksheet event.
Code:
How to use workheet event the codePlease Login or Register to view this content.
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks