Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing only Columns with a month in it (XP)

    I have a spreadsheet where Column A1 is titled "Rep Name". Then the next column, B1 has his commission, then C1 has January, 06, D1 has February, 06, and next month E1 will have March, 06. How can I write VBA code that looks at the first row in each column and if it is a month with a ", 06" then sum that column?

    Any help would be greatly appreciated.

    Thanks,
    Chris

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

    Re: Summing only Columns with a month in it (XP)

    Are the values in C1, D1 etc. date values formatted as mmmm, yy or are they text values?
    Where should the results end up?

  3. #3
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing only Columns with a month in it (XP)

    The date values are January, 06 for example which is a text value. The results should end up after the last row which will vary each month. I hope this helps you.

    Thank you for your help.
    Chris

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

    Re: Summing only Columns with a month in it (XP)

    Try this macro. It checks that the first cell in a column ends in "06" and that the last cell doesn't have a formula yet, and if so, adds a SUM formula below the column.

    Sub MakeSum()
    Dim lngLastRow As Long
    Dim lngLastCol As Long
    Dim lngCol As Long
    lngLastCol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
    For lngCol = 3 To lngLastCol
    If Right(Cells(1, lngCol), 2) = "06" Then
    lngLastRow = Cells(Cells.Rows.Count, lngCol).End(xlUp).Row
    If lngLastRow > 1 Then
    If Not Cells(lngLastRow, lngCol).HasFormula Then
    Cells(lngLastRow + 1, lngCol).FormulaR1C1 = "=SUM(R2C" & _
    lngCol & ":R" & lngLastRow & "C" & lngCol & ")"
    End If
    End If
    End If
    Next lngCol
    End Sub

  5. #5
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing only Columns with a month in it (XP)

    Hi Hans,

    Thanks a lot for your help. It works great. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Thanks again.
    Chris

Posting Permissions

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