Problem calculating sum of imported columns with Macro

[cbdesign]

Hello:

I have an excel macro that loads in html table data with inventory quantities, cost, profit, Invoice numbers and product descriptions.

Once the data is imported I need to add sum totals to the bottom of several columns (i.e. total profit, total cost, etc) excluding the headers from each calculation.

Once the totals are calculated the macro then saves the data with column totals to a new spreadsheet with the current date.

The number of rows vary since the number of items may change each time this report is run.

How can this be done (creating summary cells at the end of selected columns) with an Excel Macro?

I tried using:

With ActiveCell
Set rng = Range(.Offset(1), .Offset(1).End(xlDown))
.Formula = "=SUM(" & rng.Address & ")"
End With

but it places (for example)
"=SUM($K$3:$K$33)" into the active cell, instead of =Sum(k3:k33)

For this reason the result always = "0" in the cell where I would like the total to appear.

Please advise, help needed.


Thanks in advance,


Rich

[NateO]

You could try a procedure like the following:

Sub smr()
Dim cell As Range, rng As Range, col As Integer
Set rng = Nothing
For Each cell In [f1:k1] 'change this to your columns
col = cell.Column
Set rng = Range(Cells(3, col), Cells(65536, col).End(xlUp))
Cells(65536, col).End(xlUp).Offset(1) = "=SUM(" & _
Application.WorksheetFunction.Substitute(rng.Address, "$", "") & ")"
Next
End Sub

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-10 13:54 ]

[cbdesign]

Tried your code... and it works for removing the "$" from the formula, I also found the source of the erroneous calculation (always yielding a "0" value). It was a because one of the column cells in my sum routine contained a bad formula. Created by the "$" characters.


Thanks a Million!!!

[Jay Petrulis]

Hi,

You have your answer, but note that the default for the .Address is row/column absolute

You can avoid it directly by setting the absolute properties to False

.Address(RowAbsolute:=False, ColumnAbsolute:=False)

which can be shortened to
.Address(False, False)

There are 3 other properties, but they are less important, if only because I cannot remember them off hand!

Bye,
Jay

[NateO]

I like it Jay, much better. Looks like I went out of the box eh? From the excel help file:

Address Property


Hyperlink object (Syntax 1): Returns or sets the address of the target document. Read/write String.

Range object (Syntax 2): Returns the range reference in the language of the macro. Read-only String.

Syntax 1

expression.Address

Syntax 2

expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

expression Required. An expression that returns a Hyperlink object (Syntax 1) or a Range object (Syntax 2).

RowAbsolute Optional Variant. True to return the row part of the reference as an absolute reference. The default value is True.

ColumnAbsolute Optional Variant. True to return the column part of the reference as an absolute reference. The default value is True.

ReferenceStyle Optional Variant. Can be one of the following XlReferenceStyle constants: xlA1 or xlR1C1. Use xlA1 to return an A1-style reference. Use xlR1C1 to return an R1C1-style reference. The default value is xlA1

External Optional Variant. True to return an external reference. False to return a local reference. The default value is False.

RelativeTo Optional Variant. If RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1, you must include a starting point for the relative reference. This argument is a Range object that defines the starting point.

Remarks

If the reference contains more than one cell, RowAbsolute and ColumnAbsolute apply to all rows and columns.

Examples:

Set mc = Worksheets("Sheet1").Cells(1, 1)

MsgBox mc.Address()                              ' $A$1

MsgBox mc.Address(RowAbsolute:=False)            ' $A1

MsgBox mc.Address(ReferenceStyle:=xlR1C1)        ' R1C1

MsgBox mc.Address(ReferenceStyle:=xlR1C1, _

    RowAbsolute:=False,     _

    ColumnAbsolute:=False,  _

    RelativeTo:=Worksheets(1).Cells(3, 3))        ' R[-2]C[-2]

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-10 15:09 ]