Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Autofilter

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

    Default

    I have a workbook to keep track of Financial data for my department. Autofilter is turned on, when I change the filter I need the subtotal to change only I am using sumproduct instead of subtotal. Here is the formula =IF(ISBLANK($A$3),SUMPRODUCT(($F$10:$F$472=$F477)*($H$10:$H$472)),SUMPRODUCT(($F$10:$F$472=$F477)*($B$10:$B$472=$A$3)*($ H$10:$H$472)))

    Cell A3 is to be updated with the new criteria filter, the problem is that cell A3 will not update until I click on the worksheet. Below is my code and everything seems to work except for the updating of cell A3. Can you tell me what I am missing.



    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Changefilters

    End Sub

    Public Sub Changefilters()
    Dim w As Worksheet
    Dim filterarray()


    Set w = Worksheets("INS fy03")

    With w.AutoFilter
    With .Filters
    For f = 1 To .Count
    With .Item(f)
    If .On Then
    w.Range("A3").Value = .Criteria1
    End If
    End With
    Next
    End With

    End With

    End Sub

  2. #2

    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Try using Private Sub Worksheet_Calculate()

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,599
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    And, I'd suggest changing

    =IF(ISBLANK($A$3),SUMPRODUCT(($F$10:$F$472=$F477)*($H$10:$H$472)),SUMPRODUCT(($F$10:$F$472=$F477)*($B$10:$B$472=$A$3)*($ H$10:$H$472)))

    to

    =IF(ISBLANK($A$3),SUMIF($F$10:$F$472,$F477,$H$10:$H$472),SUMPRODUCT(($F$10:$F$472=$F477)*($B$10:$B$472=$A$3)*($H$10:$H$4 72)))

    Aladin

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
  •