filter and sum values based on three selection from dropdown

[maghari]

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

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(1, 1).Value = TheYear Or LCase(Trim(TheYear)) = "ALL" Then
If R(1, 2).Value = TheMonth Or LCase(Trim(TheMonth)) = "ALL" Then
If R(1, 3).Value = Theitem Or LCase(Trim(Theitem)) = "ALL" Then
Total = Total + R(1, 4).Value
End If
End If
End If
Next R
MsgBox Total
End Sub

thanks in advance

[snb]

Excel VBA Programming For Dummies by John Walkenbach | 9781119077398 | Paperback | Barnes & Noble (http://www.barnesandnoble.com/w/excel-vba-programming-for-dummies-john-walkenbach/1101874584)

[Paul_Hossler]

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

[maghari]

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

[Paul_Hossler]

There were some inconsistencies with UC/LC (MAY - may) and variable types (string -vs double)

27469

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

[maghari]

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

[Paul_Hossler]

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 ....

[maghari]

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:think:

[Paul_Hossler]

In your example workbook, there is only 2011 in the data and in the validation rules

27471


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

27472

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

[maghari]

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

.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 :yes

[Paul_Hossler]

Maybe




.Cells(3, 6).Value = Total

[maghari]

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 :help

[Paul_Hossler]

27474



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

[maghari]

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 :cool:

[snb]

Can you please use punctiuation marks, capitals, etc. to facilitate any reader ?

[Paul_Hossler]

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 :cool:

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


27475

[maghari]

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

[Paul_Hossler]

I can't tell exactly what you're doing, but I added extra checks just in case


27478




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

[maghari]

Awesome updating ! Thanks for your assistance and your time
Now the code works completely :clap:
Best regards,
Maghari