Hi I have a macro now that basically takes a giant heap of data on one
worksheet and based off values in a column it is diveded up into
different pages. Now on all of these pages I wanted to add certain
columns up with functions such as "sum" to do different statistics.
Now because different amounts of data can be entered once my origianl
macro sorts it I cannot just simply put in the forumulas. I can make a
forumula way at the bottom where the data will never reach to find the
sums or count the cells, ect but I need a macro that can take these
numbers and put them in the first blank row. Can anyone help? Thanks

Place your "sum" calculation in the first row of the xls and add everything
below it eg: a2:a50000. That way it deals with any length column and the
answer is alway in the same place
HTH
--
Peter
London, UK


"tnederlof" wrote:

Hi I have a macro now that basically takes a giant heap of data on one
worksheet and based off values in a column it is diveded up into
different pages. Now on all of these pages I wanted to add certain
columns up with functions such as "sum" to do different statistics.
Now because different amounts of data can be entered once my origianl
macro sorts it I cannot just simply put in the forumulas. I can make a
forumula way at the bottom where the data will never reach to find the
sums or count the cells, ect but I need a macro that can take these
numbers and put them in the first blank row. Can anyone help? Thanks

Quite frankly, Peter's suggestion is probably the easiest to implement and
works absolutely great. Just remember to adjust the copy function of your
existing macro to keep it from overwriting the formula(s) at the top.

If for some reason that suggestion is not usable (maybe you have some
curmudgons in the office that simply MUST have the total at the bottom of the
data) here's a routine that shows a line of code that will put a formula,
SUM() in this example, into the first empty cell below all of the data in a
column (I used column D in the example - change as needed). Code doesn't
have to be in its own Sub, you could include that single line once or many
times in your existing macro, depending on how many columns you need to come
up with formulas for:

Sub PutFormulaAtEndOfData()
'using column D just as an example.
'just change the "D" and D1 references for each specific column on a sheet
'if using Excel 2007, use Rows.CountLarge instead of Rows.Count

Range(Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Address).Formula = _
"=SUM(D1:" & Range("D" & Rows.Count).End(xlUp).Address & ")"

End Sub


"tnederlof" wrote:

Hi I have a macro now that basically takes a giant heap of data on one
worksheet and based off values in a column it is diveded up into
different pages. Now on all of these pages I wanted to add certain
columns up with functions such as "sum" to do different statistics.
Now because different amounts of data can be entered once my origianl
macro sorts it I cannot just simply put in the forumulas. I can make a
forumula way at the bottom where the data will never reach to find the
sums or count the cells, ect but I need a macro that can take these
numbers and put them in the first blank row. Can anyone help? Thanks

Just my two cents - you don't need the address in the first half of the
statement (but it will still work either way) and the address in the second
half returns an absolute reference - which could be a problem if he copies it
across to the other columns (as you said - it may not be an option depending
on the data. I'm just pointing it out in case that's the direction the OP
takes to populate the rest of the columns).

Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Formula = _
"=SUM(D1:" & Range("D" & Rows.Count).End(xlUp).Address(False, False) & ")"


"JLatham" wrote:

Quite frankly, Peter's suggestion is probably the easiest to implement and
works absolutely great. Just remember to adjust the copy function of your
existing macro to keep it from overwriting the formula(s) at the top.

If for some reason that suggestion is not usable (maybe you have some
curmudgons in the office that simply MUST have the total at the bottom of the
data) here's a routine that shows a line of code that will put a formula,
SUM() in this example, into the first empty cell below all of the data in a
column (I used column D in the example - change as needed). Code doesn't
have to be in its own Sub, you could include that single line once or many
times in your existing macro, depending on how many columns you need to come
up with formulas for:

Sub PutFormulaAtEndOfData()
'using column D just as an example.
'just change the "D" and D1 references for each specific column on a sheet
'if using Excel 2007, use Rows.CountLarge instead of Rows.Count

Range(Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Address).Formula = _
"=SUM(D1:" & Range("D" & Rows.Count).End(xlUp).Address & ")"

End Sub


"tnederlof" wrote:

Hi I have a macro now that basically takes a giant heap of data on one
worksheet and based off values in a column it is diveded up into
different pages. Now on all of these pages I wanted to add certain
columns up with functions such as "sum" to do different statistics.
Now because different amounts of data can be entered once my origianl
macro sorts it I cannot just simply put in the forumulas. I can make a
forumula way at the bottom where the data will never reach to find the
sums or count the cells, ect but I need a macro that can take these
numbers and put them in the first blank row. Can anyone help? Thanks

Correct on both counts. The .Address ended up in the left half because I'd
originally set up a variable to grab the address and then use that variable
in the left side - then I decided to do it in one step instead of 2 and so I
got the .Address part when copying the original pre-step.
As for the absolute part of it, I kind of figured (there I go, assuming
again) that if he was doing this in code, that he wouldn't be revisiting the
sheet later.

But I stick to my guns that Peter's way is a great way.

"JMB" wrote:

Just my two cents - you don't need the address in the first half of the
statement (but it will still work either way) and the address in the second
half returns an absolute reference - which could be a problem if he copies it
across to the other columns (as you said - it may not be an option depending
on the data. I'm just pointing it out in case that's the direction the OP
takes to populate the rest of the columns).

Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Formula = _
"=SUM(D1:" & Range("D" & Rows.Count).End(xlUp).Address(False, False) & ")"


"JLatham" wrote:

Quite frankly, Peter's suggestion is probably the easiest to implement and
works absolutely great. Just remember to adjust the copy function of your
existing macro to keep it from overwriting the formula(s) at the top.

If for some reason that suggestion is not usable (maybe you have some
curmudgons in the office that simply MUST have the total at the bottom of the
data) here's a routine that shows a line of code that will put a formula,
SUM() in this example, into the first empty cell below all of the data in a
column (I used column D in the example - change as needed). Code doesn't
have to be in its own Sub, you could include that single line once or many
times in your existing macro, depending on how many columns you need to come
up with formulas for:

Sub PutFormulaAtEndOfData()
'using column D just as an example.
'just change the "D" and D1 references for each specific column on a sheet
'if using Excel 2007, use Rows.CountLarge instead of Rows.Count

Range(Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Address).Formula = _
"=SUM(D1:" & Range("D" & Rows.Count).End(xlUp).Address & ")"

End Sub


"tnederlof" wrote:

Hi I have a macro now that basically takes a giant heap of data on one
worksheet and based off values in a column it is diveded up into
different pages. Now on all of these pages I wanted to add certain
columns up with functions such as "sum" to do different statistics.
Now because different amounts of data can be entered once my origianl
macro sorts it I cannot just simply put in the forumulas. I can make a
forumula way at the bottom where the data will never reach to find the
sums or count the cells, ect but I need a macro that can take these
numbers and put them in the first blank row. Can anyone help? Thanks

I know how that goes.


"JLatham" wrote:

Correct on both counts. The .Address ended up in the left half because I'd
originally set up a variable to grab the address and then use that variable
in the left side - then I decided to do it in one step instead of 2 and so I
got the .Address part when copying the original pre-step.
As for the absolute part of it, I kind of figured (there I go, assuming
again) that if he was doing this in code, that he wouldn't be revisiting the
sheet later.

But I stick to my guns that Peter's way is a great way.

"JMB" wrote:

Just my two cents - you don't need the address in the first half of the
statement (but it will still work either way) and the address in the second
half returns an absolute reference - which could be a problem if he copies it
across to the other columns (as you said - it may not be an option depending
on the data. I'm just pointing it out in case that's the direction the OP
takes to populate the rest of the columns).

Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Formula = _
"=SUM(D1:" & Range("D" & Rows.Count).End(xlUp).Address(False, False) & ")"


"JLatham" wrote:

Quite frankly, Peter's suggestion is probably the easiest to implement and
works absolutely great. Just remember to adjust the copy function of your
existing macro to keep it from overwriting the formula(s) at the top.

If for some reason that suggestion is not usable (maybe you have some
curmudgons in the office that simply MUST have the total at the bottom of the
data) here's a routine that shows a line of code that will put a formula,
SUM() in this example, into the first empty cell below all of the data in a
column (I used column D in the example - change as needed). Code doesn't
have to be in its own Sub, you could include that single line once or many
times in your existing macro, depending on how many columns you need to come
up with formulas for:

Sub PutFormulaAtEndOfData()
'using column D just as an example.
'just change the "D" and D1 references for each specific column on a sheet
'if using Excel 2007, use Rows.CountLarge instead of Rows.Count

Range(Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Address).Formula = _
"=SUM(D1:" & Range("D" & Rows.Count).End(xlUp).Address & ")"

End Sub


"tnederlof" wrote:

Hi I have a macro now that basically takes a giant heap of data on one
worksheet and based off values in a column it is diveded up into
different pages. Now on all of these pages I wanted to add certain
columns up with functions such as "sum" to do different statistics.
Now because different amounts of data can be entered once my origianl
macro sorts it I cannot just simply put in the forumulas. I can make a
forumula way at the bottom where the data will never reach to find the
sums or count the cells, ect but I need a macro that can take these
numbers and put them in the first blank row. Can anyone help? Thanks