VBA Code to loop through Array and with criteria

[Wafee]

I have below code where we arrived at the list based on criteria and I have assigned it to an array called arr1. Now I am looking to apply a filter in "K" colum to select "Yes" alone loop through this array of items which are in "I" column and get the total sum from R column. I am fine if array is not to be used and we can loop through the list.

Sub Dcount()
Dim Lastrow As Long
Dim Rng As Range
Dim List As Object
Dim Listcount As Long
Dim arr1 as Variant
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set List = CreateObject("Scripting.Dictionary")
With Range("A1:I1")
.AutoFilter 1, "Temp", xlOr, "tem"
.AutoFilter 5, "MX"
End With
For Each Rng In Range("I2:I" & Lastrow).SpecialCells(xlVisible)
List(Rng.Value) = empty
Next Rng
ActiveSheet.AutoFilterMode = False
arr1 = Application.Transpose(Array(List.Keys, List.Items))

 

[DanteAmor]

We can go through the data without filtering, we use an array to read the data and save in dic those that meet the condition and at the same time sum column R.

Try this:

Sub Dcount()
  Dim dic As Object, i As Long, j As Long
  Dim a As Variant, arr1 As Variant
  
  a = Range("A2", Range("R" & Rows.Count).End(3)).Value2
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a, 1)
    If (a(i, 1) = "Temp" Or a(i, 1) = "tem") And a(i, 5) = "MX" And a(i, 11) = "Yes" Then
      dic(a(i, 9)) = dic(a(i, 9)) + a(i, 18)
    End If
  Next i
  arr1 = Application.Transpose(Array(dic.Keys, dic.Items))
End Sub

 

[Wafee]

We can go through the data without filtering, we use an array to read the data and save in dic those that meet the condition and at the same time sum column R.

Try this:

Sub Dcount()
  Dim dic As Object, i As Long, j As Long
  Dim a As Variant, arr1 As Variant
  
  a = Range("A2", Range("R" & Rows.Count).End(3)).Value2
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a, 1)
    If (a(i, 1) = "Temp" Or a(i, 1) = "tem") And a(i, 5) = "MX" And a(i, 11) = "Yes" Then
      dic(a(i, 9)) = dic(a(i, 9)) + a(i, 18)
    End If
  Next i
  arr1 = Application.Transpose(Array(dic.Keys, dic.Items))
End Sub

Thanks DanteAmor, that was useful but my requirement here is bit different. I have two values in F column(MX,CX) so we are finding the list with filtering "MX" in that. Now for the resulted list or array I want to know Sum from R column by filtering "CX" from F column and vice versa. Hope it is clear now. Sorry if it is confusing.

 

[DanteAmor]

Sorry, I am not understanding.
Better explain what the ultimate goal is.
Put a range of your data.
And in another section the range of results you want.
Use XL2BB tool, see my signature.

 

[Wafee]

Sorry, I am not understanding.
Better explain what the ultimate goal is.
Put a range of your data.
And in another section the range of results you want.
Use XL2BB tool, see my signature.

Hi DanteAmor,

Assume we got the arr1 with items. Forget the code before that. Now for these items I want total sum from R column when "CX" is filtered from F column. In worksheet arr1 items will be there in "I" column. Ultimate goal will be to filter "CX" from F column and loop through arr1 and map the values to "I" column and get their sum from "R" Column. There might be multiple lines for a single value.hope I am clear.

 

[DanteAmor]

Sorry, but it's not clear to me.
I would like to see a sample of your unfiltered data, what criteria must be met, what you want to sum, what is the result you want and where you want the result.
Something like this:

varios 05jun2020 StatusBar.xlsm
	A					F			I									R
1	Head1					Head6			Head9									Head18
2	Temp					CX			Item1									100
3	Temp					CX			Item2									112
4	Temp					MX			Item3									101
5	Temp					MX			Item4									113
6	Temp					MX			Item5									102
7	Temp					CX			Item6									114
8	Temp					CX			Item1									103
9	Temp					CX			Item2									115
10	Temp					CX			Item3									104
11	Temp					MX			Item4									116
12	Temp					MX			Item5									105
13	Temp					MX			Item6									117
14	Temp					MX			Item1									106
15	Temp					MX			Item2									118
16	Temp					MX			Item3									107
17	Temp					CX			Item4									119
18	Temp					CX			Item5									108
19	Temp					CX			Item6									120
20	Temp					CX			Item1									109
Sheet2

varios 05jun2020 StatusBar.xlsm
	T	U
3	RESULT	Sum Head18
4	CX	1104
5	Item1	312
6	Item2	227
7	Item3	104
8	Item4	119
9	Item5	108
10	Item6	234
11	Total	1104
Sheet2

(The result can be obtained with a pivot table)

 

[Wafee]

Sorry, but it's not clear to me.
I would like to see a sample of your unfiltered data, what criteria must be met, what you want to sum, what is the result you want and where you want the result.
Something like this:

varios 05jun2020 StatusBar.xlsm
	A					F			I									R
1	Head1					Head6			Head9									Head18
2	Temp					CX			Item1									100
3	Temp					CX			Item2									112
4	Temp					MX			Item3									101
5	Temp					MX			Item4									113
6	Temp					MX			Item5									102
7	Temp					CX			Item6									114
8	Temp					CX			Item1									103
9	Temp					CX			Item2									115
10	Temp					CX			Item3									104
11	Temp					MX			Item4									116
12	Temp					MX			Item5									105
13	Temp					MX			Item6									117
14	Temp					MX			Item1									106
15	Temp					MX			Item2									118
16	Temp					MX			Item3									107
17	Temp					CX			Item4									119
18	Temp					CX			Item5									108
19	Temp					CX			Item6									120
20	Temp					CX			Item1									109
Sheet2

varios 05jun2020 StatusBar.xlsm
	T	U
3	RESULT	Sum Head18
4	CX	1104
5	Item1	312
6	Item2	227
7	Item3	104
8	Item4	119
9	Item5	108
10	Item6	234
11	Total	1104
Sheet2

(The result can be obtained with a pivot table)

This is exactly what I need. So consider we have distinct list from column "I" when we filter "MX" from column F which is array - arr1 = (item1,item2,item3,item4,item5,item6) - in my case arr1 will have few items but not all the possible items. Now for the above list I want their sum from R column when "CX" is filtered in F column. I can't go to pivots because I am already writing a code to complete the report and this is one part of it and I have data of around 2 lac lines.

 

[DanteAmor]

According to my example, the following filters by MX and puts the results in arr1 and in the range T2 onwards.

Sub Sum_Column_R()
  Dim dic As Object, i As Long, j As Long
  Dim a As Variant, arr1 As Variant
  
  a = Range("A2", Range("R" & Rows.Count).End(3)).Value2
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a, 1)
    If a(i, 6) = "MX" Then
      dic(a(i, 9)) = dic(a(i, 9)) + a(i, 18)
    End If
  Next i
  
  arr1 = Application.Transpose(Array(dic.Keys, dic.Items))
  Range("T2").Resize(UBound(arr1, 1), UBound(arr1, 2)).Value = arr1
End Sub

 

[Wafee]

According to my example, the following filters by MX and puts the results in arr1 and in the range T2 onwards.

Sub Sum_Column_R()
  Dim dic As Object, i As Long, j As Long
  Dim a As Variant, arr1 As Variant
  
  a = Range("A2", Range("R" & Rows.Count).End(3)).Value2
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a, 1)
    If a(i, 6) = "MX" Then
      dic(a(i, 9)) = dic(a(i, 9)) + a(i, 18)
    End If
  Next i
  
  arr1 = Application.Transpose(Array(dic.Keys, dic.Items))
  Range("T2").Resize(UBound(arr1, 1), UBound(arr1, 2)).Value = arr1
End Sub

Hi, Until this I have also done my code. Now what I want is, consider Arr1 = (item1,item4,item6). Now I want to filter CX in sheet and find the total sum from R column against my arr1 items. In this case total sum would be 665. item1 - 100,103,109; item4- 119; item6 - 114, 120.

 

[DanteAmor]

Sorry, but I'm not understanding what the ultimate goal is.
Do you only want the sum according to some criteria?