Consulting

Results 1 to 19 of 19

Thread: filter and sum values based on three selection from dropdown

  1. #1

    filter and sum values based on three selection from dropdown

    hello
    i try finding code show data when i select from e1,f1,g1 and sum values the lastrow this is what i got but doesn't work
    PHP Code:
    Sub AAA()    Dim R As Range   
     Dim DataRange 
    As Range   
     Dim Total 
    As Double   
     Dim TheYear 
    As String    
    Dim TheMonth 
    As String    
    Dim Theitem 
    As String       ' get your values from the linked cells.    
    TheYear = Range("A1").Value   
     TheMonth = Range("F1").Value    
    Theitem = Range("G1").Value      
     Set DataRange = Worksheets("INV").Range("A1:D10") '
    << CHANGE   
     
    For Each R In DataRange.Columns(1).Cells        
    If R(11).Value TheYear Or LCase(Trim(TheYear)) = "ALL" Then           
     
    If R(12).Value TheMonth Or LCase(Trim(TheMonth)) = "ALL" Then              
      
    If R(13).Value Theitem Or LCase(Trim(Theitem)) = "ALL" Then                    
    Total 
    Total R(14).Value          
          End 
    If           
     
    End If        
    End If    
    Next R    
    MsgBox Total
    End Sub 
    thanks in advance
    Attached Files Attached Files
    Last edited by maghari; 11-19-2020 at 02:55 AM.

  2. #2

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Possibly a more helpful suggestion ...

    1. In ...

    LCase(Trim(TheYear)) = "ALL"
    ... since you lower case TheYear, it will NEVER, EVER, EVER = "ALL"


    2. In ...

    TheYear = Range ("A1")
    ... it should be E1


    3. Brute force, and not very elegant, but easiest to understand

    Option Explicit
    
    
    Private Sub GetTotal()
        Dim iRow As Long
        Dim Total As Double
        Dim TheYear As Variant, TheMonth As Variant
        Dim Theitem As String
       
        
        With ActiveSheet
             ' get your values from the linked cells.
             TheYear = .Range("E1").Value
             TheMonth = .Range("F1").Value
             Theitem = .Range("G1").Value
            
             For iRow = 2 To ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
                If .Cells(iRow, 1).Value = TheYear Or LCase(Trim(TheYear)) = "all" Then
                    If .Cells(iRow, 2).Value = TheMonth Or LCase(Trim(TheMonth)) = "all" Then
                        If .Cells(iRow, 3).Value = Theitem Or LCase(Trim(Theitem)) = "all" Then
                            Total = Total + .Cells(iRow, 4).Value
                        End If
                    End If
                End If
            Next iRow
        End With
        
        MsgBox Total
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    hi, paul actually updating code works only when select all but when i select month like May or select control like ast it gives me 0 and i would summing value in the last row

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    There were some inconsistencies with UC/LC (MAY - may) and variable types (string -vs double)

    Capture.JPG

    This should fix it

    Option Explicit
    Option Compare Text
    
    
    Sub GetTotal()
        Dim iRow As Long
        Dim Total As Double
        Dim TheYear As Variant, TheMonth As Variant
        Dim Theitem As String
       
        
        With ActiveSheet
             ' get your values from the linked cells.
             TheYear = Trim(.Range("E1").Value)
             TheMonth = Trim(.Range("F1").Value)
             Theitem = Trim(.Range("G1").Value)
            
             For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
                If CStr(.Cells(iRow, 1).Value) = TheYear Or TheYear = "all" Then
                    If .Cells(iRow, 2).Value = TheMonth Or TheMonth = "all" Then
                        If .Cells(iRow, 3).Value = Theitem Or Theitem = "all" Then
                            Total = Total + .Cells(iRow, 4).Value
                        End If
                    End If
                End If
            Next iRow
            
            .Cells(1, 4).End(xlDown).Offset(1, 0).Value = Total
        
        End With
    End Sub
    Edit - added total to last row
    Attached Files Attached Files
    Last edited by Paul_Hossler; 11-19-2020 at 11:19 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    nice updating thanks paul about the total i would be under last row is filling not every time i select from dropdown it move the total under it as your picture the total should in d9 if i change selection it moves d10 this is not what i would i want keeping the total in d9

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by maghari View Post
    nice updating thanks paul about the total i would be under last row is filling not every time i select from dropdown it move the total under it as your picture the total should in d9 if i change selection it moves d10 this is not what i would i want keeping the total in d9
    Replace

    .Cells(1, 4).End(xlDown).Offset(1, 0).Value = Total

    with

    .Cells(9, 4).Value = Total
    If you add more data rows, this will replace whatever is in row 9, but if that's what you want ....
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    thanks paul but i note if i add another year 2012,2013..etc it doesn't sum it gives me 0 do you have any idea

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    In your example workbook, there is only 2011 in the data and in the validation rules

    Capture.JPG


    Just changing some data and updating the data validation, it seems to work

    Capture1.JPG

    Attach a new example workbook with what ever you're seeing
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    yes you're right my mistake when i select a new year i selected another options are not existed that's why gives me 0 so i have last thing i would summing dynamically as you see maybe filling data changes after row 9
    HTML Code:
    .Cells(9, 4).Value = Total
    in this case this is not useful if is possible sum after lastrow filling data maybe after 12 or 15 and so on it based filling data

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Maybe


    .Cells(3, 6).Value = Total
    
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    thanks paul but this is not what i want just after lastrow contains value for instance if the lastrow in d9 then show summing in d10 if the lastrow in d11 then summing in d12 it is based on filling lastrow

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Capture.JPG

    Option Explicit
    Option Compare Text
    
    
    Sub GetTotal()
        Dim iRow As Long
        Dim Total As Double
        Dim TheYear As Variant, TheMonth As Variant
        Dim Theitem As String
       
        
        With ActiveSheet
             ' get your values from the linked cells.
             TheYear = Trim(.Range("E1").Value)
             TheMonth = Trim(.Range("F1").Value)
             Theitem = Trim(.Range("G1").Value)
            
             For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
                If CStr(.Cells(iRow, 1).Value) = TheYear Or TheYear = "all" Then
                    If .Cells(iRow, 2).Value = TheMonth Or TheMonth = "all" Then
                        If .Cells(iRow, 3).Value = Theitem Or Theitem = "all" Then
                            Total = Total + .Cells(iRow, 4).Value
                        End If
                    End If
                End If
            Next iRow
            
            .Cells(1, 1).End(xlDown).Offset(1, 3).Value = Total   ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
        
        End With
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    thanks again Paul yes this is what i'm looking for but it remains a problem when i run macro repeatedly it change total every time ,I would sum one time because i expect pressing Unintentionally and gives me wrong total after you if is possible ,it is summing total one time not change after this what ever i press the macro repeatedly

  15. #15
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Can you please use punctiuation marks, capitals, etc. to facilitate any reader ?

  16. #16
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by maghari View Post
    thanks again Paul yes this is what i'm looking for but it remains a problem when i run macro repeatedly it change total every time ,I would sum one time because i expect pressing Unintentionally and gives me wrong total after you if is possible ,it is summing total one time not change after this what ever i press the macro repeatedly
    If I understand, I'm afraid I don't see that

    I can click the button multiple times and the total doesn't change

    What EXACTLY are you doing if you see that


    Capture.JPG
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  17. #17
    you're right Paul , actually I don't focus very good , My mistake when I select all in year
    This changes continuously , Is there way to fix it please ?

  18. #18
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I can't tell exactly what you're doing, but I added extra checks just in case


    Capture.JPG


    Option Explicit
    Option Compare Text
    
    
    Sub GetTotal()
        Dim iRow As Long
        Dim Total As Double
        Dim TheYear As String, TheMonth As String, Theitem As String
        Dim r As Range
       
        
        With ActiveSheet
            Set r = .Cells(1, 1)
            Set r = Range(r, r.End(xlDown).Offset(0, 3))
        
             ' get your values from the linked cells.
             TheYear = Trim(.Range("E1").Value)
             TheMonth = Trim(.Range("F1").Value)
             Theitem = Trim(.Range("G1").Value)
            
             For iRow = 2 To r.Rows.Count
                If .Cells(iRow, 1).Value <> TheYear And TheYear <> "all" Then GoTo NextLine
                If .Cells(iRow, 2).Value <> TheMonth And TheMonth <> "all" Then GoTo NextLine
                If .Cells(iRow, 3).Value <> Theitem And Theitem <> "all" Then GoTo NextLine
                
                Total = Total + .Cells(iRow, 4).Value
    NextLine:
            Next iRow
            
            .Cells(1, 1).End(xlDown).Offset(1, 3).Value = Total
        
        End With
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  19. #19
    Awesome updating ! Thanks for your assistance and your time
    Now the code works completely
    Best regards,
    Maghari

Posting Permissions

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