xlUp to sum using a macro

[hawtin]

Hi,

I would like to sum varying length columns.
On the spreadsheet I would press alt,= and it selects the range automatically.
If I record a macro, it shows this as a relative range, however, it does not change the lenth of the column to be added.

Here is an example of one of my fails:
ActiveCell.FormulaR1C1 = "=SUM(Range(d, d.End(xlUp)))"

This may give you an idea of what I am trying to do though.

Thanks,

 

[mirabeau]

Hi,

I would like to sum varying length columns.
On the spreadsheet I would press alt,= and it selects the range automatically.
If I record a macro, it shows this as a relative range, however, it does not change the lenth of the column to be added.

Here is an example of one of my fails:
ActiveCell.FormulaR1C1 = "=SUM(Range(d, d.End(xlUp)))"

This may give you an idea of what I am trying to do though.

Thanks,

in your example:
What is d?
If it's some cell chosen for some reason, then just how does this fail?
Does it do something you don't want - what?
Does it not do something you do want - what?
Other???

 

[hawtin]

d is defined as a range.

ActiveCell.FormulaR1C1 = "=SUM(Range(d, d.End(xlUp)))"

This doesnt work at all, because it puts "=SUM(Range(d, d.End(xlUp)))" in the active cell, which is not a valid value in a cell.

What I am trying to do is have a macro that formats a report, and part of that format it to insert totals at the bottom of columns of numbers, however, the length of the column varies.

Thanks.

 

[Scott Huish]

What range is d?

Why are you using ActiveCell?

Where do you want the SUM?

Do you really need a formula in the cell or just the result?

 

[hawtin]

I am using Activecell because I get the report as a text file, which I then go through and add formating like extra lines, removing page headers, and insert sub totals.

So as I go down the page I take out page headers, and insert blank lines to create blocks of data that can be subtotalled.

When I do it manually, I just hit Alt= and it inserts the sum() formula and it automatically selects the range to the top of the column.

I have a macro to do everything except insert sub totals.

I want to do this in a macro, and I don't mind if it is the value or a formula.

I dont know how to insert a copy of the spreadsheet to show you. sorry.

Thanks,

 

[mirabeau]

d is defined as a range.

ActiveCell.FormulaR1C1 = "=SUM(Range(d, d.End(xlUp)))"

This doesnt work at all, because it puts "=SUM(Range(d, d.End(xlUp)))" in the active cell, which is not a valid value in a cell.

What I am trying to do is have a macro that formats a report, and part of that format it to insert totals at the bottom of columns of numbers, however, the length of the column varies.

Thanks.

If you want to use that sort of approach, with just the value and not the formula, you could use something like

Set d = Range("H4")
ActiveCell = Application.Sum(Range(d, d.End(xlUp)))

If you want more information on the type of problem you raise, and to show how to include the formula if need be, then you might usefully peruse the thread
http://www.mrexcel.com/forum/showthread.php?t=523236

 

[hawtin]

Thanks Mirabeau.
I will have a look at all the others in the other thread, but this one seems simple enough for my purpose.