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

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

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