Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need to apply code to filter & sum (97)

    Need to apply code to filter & sum
    I have a workbook in excel 97 with several sheets in the same format. On the workbooks open event I

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need to apply code to filter & sum (97)

    Arage, you don't really need all that code: just find the last row and do a conditional sum. HTH --Sam
    <pre>Option Explicit
    Sub SumPerH()
    Dim lastRow As Integer
    lastRow = Range("H9").End(xlDown).Row + 1
    With Cells(lastRow, 10)
    .FormulaArray = "=SUM(IF(ISNUMBER(R[-" & lastRow - 9 & _
    "]C8:R[-1]C8),R[-" & lastRow - 9 & "]C:R[-1]C,0))"
    .AutoFill Destination:=Range(Cells(lastRow, 10), Cells(lastRow, 16)), _
    Type:=xlFillDefault
    End With
    End Sub</pre>


    PS, I didn't figure this out myself, I just recorded a macro on some sample data and then substituted lastRow in the needed places.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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