Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Brisbane, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing coumns of differing lengths (XL2002)

    Hi. I am trying to put together a macro to import a csv file and turn it into a spreadsheet. Most has gone well, but I am stumped (again)! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    The file is imported, formatted, etc, but I can't seem to put together a formula to go to the bottom of a column and sum it (and the next, and next etc.)
    The column lengths will be different each time I run the report that creates the csv file, so the code has to figure that out. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summing coumns of differing lengths (XL2002)

    The following macro is not meant as a definitive solution, but as a starting point. You will have to adapt it to your situation.

    Sub SetColumnSums()
    Dim lngCols As Long
    Dim lngRow As Long
    Dim lngCol As Long
    lngCols = Range("IV1").End(xlToLeft).Column
    For lngCol = 1 To lngCols
    lngRow = Cells(65536, lngCol).End(xlUp).Row
    If lngRow < 65536 Then
    Cells(lngRow + 1, lngCol).FormulaR1C1 = _
    "=Sum(R1C" & lngCol & ":R" & lngRow & "C" & lngCol & ")"
    End If
    Next lngCol
    End Sub

    If you have certain columns that shouldn't be summed, modify the line For lngCol = 1 To lngCols accordingly. For instance, if the first column contains labels, change it to For lngCol = 2 To lngCols.

    If you have certain rows that shouldn't be summed, modify the formula that is being generated. For example, if the first two rows contain labels, change "=Sum(R1C" to "=Sum(R3C".

    Note: the underscore _ in the line ending with FormulaR1C1 = _ is a continuation character; it indicates that the instruction is continued on the next line. The underscore must be preceded by a space.

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Brisbane, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing coumns of differing lengths (XL2002)

    That was a thing of beauty!! Thank you so much. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    All that concatenating of the parts of the formula was a little unexpected.
    Worked like a charm and I will get a lot of mileage out of this.

    Thanks, again,

    Dan

Posting Permissions

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