Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Total to End of columns
Hi there, my query is very complicated and hope someone can help me!!!
I have created 2 tabs, one is for data entry (Man days entry) and the second one is for calculations (Rate * Days) and variance calculations. For the calculation tab, i have created a macro and so far it is working fine. Basically it says if there is an entry in the "Data Entry" Tab in Cell A2, then calcuate in the "Costs" tab and put the result in Cell A2. The total file is trying to put monthly budget, actual and forecast for the next 5 years in one tab, hence it is Column A to IE. I use Do Until, IF function and looping the code to calculate for me. However, i am stuck not knowing how to add rows to the end of the "Calculated Cost" within the calculated tab as user can insert as many lines as they want in the "Data Entry Tab". i.e. the number of rows varies - it could be from 20 rows to 50 rows, etc I would like: 1. add a which total up Cell A2:Cell A (wherever) underneath the costs for each month 2. add a blank row underneath the total and add another row again which puts in a subtotal function (as user uses autofilter for specific departments/grades/name, it is necessary for them to have this function). I would give you the code I've entered so far, but it is very long and think it is not useful here...but I can supply it if this is necessary!! I hope someone can help me, I am sorry if I have confused and over complicated this!! Thanks a lot in advance Vivi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Total to End of columns
You can get the last filled row using the below code and then use that in the formula or for calculation 'Last row filled in Column A lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row If this post helps click Yes --------------- Jacob Skaria "vivi" wrote: Hi there, my query is very complicated and hope someone can help me!!! I have created 2 tabs, one is for data entry (Man days entry) and the second one is for calculations (Rate * Days) and variance calculations. For the calculation tab, i have created a macro and so far it is working fine. Basically it says if there is an entry in the "Data Entry" Tab in Cell A2, then calcuate in the "Costs" tab and put the result in Cell A2. The total file is trying to put monthly budget, actual and forecast for the next 5 years in one tab, hence it is Column A to IE. I use Do Until, IF function and looping the code to calculate for me. However, i am stuck not knowing how to add rows to the end of the "Calculated Cost" within the calculated tab as user can insert as many lines as they want in the "Data Entry Tab". i.e. the number of rows varies - it could be from 20 rows to 50 rows, etc I would like: 1. add a which total up Cell A2:Cell A (wherever) underneath the costs for each month 2. add a blank row underneath the total and add another row again which puts in a subtotal function (as user uses autofilter for specific departments/grades/name, it is necessary for them to have this function). I would give you the code I've entered so far, but it is very long and think it is not useful here...but I can supply it if this is necessary!! I hope someone can help me, I am sorry if I have confused and over complicated this!! Thanks a lot in advance Vivi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add Total to End of columns
Here are two ways to sum a variable range, placing the result in the first
empty cell at the end of the range: Sub SumAtEnd() firstempty = Range("A2").End(xlDown).Row + 1 Range("A" & firstempty).Formula = "=SUM(A2:A" & firstempty - 1 & ")" End Sub Sub VariableSum() Dim lastrow As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1 Cells(lastrow, "A").FormulaR1C1 _ = "=sum(r2c:r[-1]c)" End Sub Note, the summing starts in Call A2. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jacob Skaria" wrote: You can get the last filled row using the below code and then use that in the formula or for calculation 'Last row filled in Column A lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row If this post helps click Yes --------------- Jacob Skaria "vivi" wrote: Hi there, my query is very complicated and hope someone can help me!!! I have created 2 tabs, one is for data entry (Man days entry) and the second one is for calculations (Rate * Days) and variance calculations. For the calculation tab, i have created a macro and so far it is working fine. Basically it says if there is an entry in the "Data Entry" Tab in Cell A2, then calcuate in the "Costs" tab and put the result in Cell A2. The total file is trying to put monthly budget, actual and forecast for the next 5 years in one tab, hence it is Column A to IE. I use Do Until, IF function and looping the code to calculate for me. However, i am stuck not knowing how to add rows to the end of the "Calculated Cost" within the calculated tab as user can insert as many lines as they want in the "Data Entry Tab". i.e. the number of rows varies - it could be from 20 rows to 50 rows, etc I would like: 1. add a which total up Cell A2:Cell A (wherever) underneath the costs for each month 2. add a blank row underneath the total and add another row again which puts in a subtotal function (as user uses autofilter for specific departments/grades/name, it is necessary for them to have this function). I would give you the code I've entered so far, but it is very long and think it is not useful here...but I can supply it if this is necessary!! I hope someone can help me, I am sorry if I have confused and over complicated this!! Thanks a lot in advance Vivi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total column changes colors when total equals sum of other columns | New Users to Excel | |||
Average the total of 3 columns | Excel Discussion (Misc queries) | |||
Total width of several columns. | Excel Worksheet Functions | |||
how do i add up 3 columns to get a total | New Users to Excel | |||
Total No of Columns in Worksheet | Excel Programming |