Problem calculating sum of imported columns with Macro

cbdesign

New Member
Joined
May 9, 2002
Messages
2
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
You could try a procedure like the following:

Code:
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-10 13:54
 
Upvote 0
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!!!
 
Upvote 0
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
 
Upvote 0
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:<pre>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]</pre>

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-10 15:09
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top