Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Problem calculating sum of imported columns with Macro

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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




  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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, NateO

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

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •