Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: I need help with this

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Trying to sum a column of data and fill the next cell below with this value. But I each column length is unknown beforehand.

    Is this close?
    ============================

    Dim LastR As Integer, CurrRow As Integer
    Dim PercRange As Range, Thing
    Sheets("Sheet1").Activate 'Activate sheet
    Range("A1").Select 'Select top of column
    Selection.End(xlDown).Select 'Find bottom of column
    LastR = ActiveCell.Row 'Get row number
    Range(Cells(2, 1), Cells(LastR, 1)).Select

    'sum entries in column B that are >10
    ' =SUM(IF(B10:B50>10,B10:B50,0))
    'A = Application.WorksheetFunction.sum(a1, a531)
    'a = Application.WorksheetFunction.sum(Cells(a, 1), Cells(a, 531))

    'Range("A1:A7").Select
    'Range("A7").Activate
    'ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
    'Range("C12").Select
    'ActiveCell.FormulaR1C1 = "=SUM(R[-11]C[-2]:R[-6]C[-2])"

    'Range("A1:A7").Select
    Range("A532").Activate
    ActiveCell.FormulaR1C1 = "=SUM(R[-531]C:R[-1]C)"
    Range("A532").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-531]C:R[-1]C)"

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al or Mark W. any ideas

  3. #3
    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,

    I'm not Al or Mark W, but try:

    '--------
    Sub sum_col_a_data()
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row

    ' this loads the SUM function
    Cells(lastrow + 1, 1) = "=SUM(R[-" & lastrow & "]C:R[-1]C)"

    ' this loads the SUMIF function
    Cells(lastrow + 1, 1) = "=SUMIF(R[-" & lastrow & "]C:R[-1]C,"">10"")"

    End Sub
    '------------

    I don't understand which of these you need from your code.

    HTH,
    Jay


  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay,

    This works perfect, but all of the summations are going to the end of A column. If I'm summing up column B, I need the sum to be at the end of column B. I put the code into the macro and assigned it a shortcut key. I went to the header of each column and ran it and the summation for each column goes at the bottom of A....

  5. #5
    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,

    This will do what you want. You need to decide which formula (or write another) you want loaded. This one actually loads them both, but the SUMIF overwrites the SUM function.

    '-----------
    Sub sum_col_data()
    Dim lastrow As Long, x As Long
    Dim ColCount As Integer

    With ActiveSheet
    .UsedRange
    ColCount = .Cells.SpecialCells(xlCellTypeLastCell).Column
    End With

    For x = 1 To ColCount
    lastrow = Cells(Rows.Count, x).End(xlUp).Row
    Cells(lastrow + 1, x) = "=SUM(R[-" & lastrow & "]C:R[-1]C)"
    Cells(lastrow + 1, x) = "=SUMIF(R[-" & lastrow & "]C:R[-1]C,"">10"")"
    Next x
    End Sub
    '--------------

    Bye,
    Jay

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Jay you're a lifesaver. I really appreciate your help

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
  •