Dictionary For loop with Multiple If and range

[kirankoushik]

Hello All,

I am trying to create a count for each item in the dictionary, where the values in 2 other columns are > 0

I am only getting the dictionary count as output each time. Please help me understand where I am going wrong.

    dic.CompareMode = vbTextCompare
    Set rRngVisible = Sheets("ScrewPoints").Range("A2").CurrentRegion
        
    For Each rRow In rRngVisible.Rows
            dic(rRow.Cells(2, 6).Value) = ""
    Next rRow
    
    Sheets("Total").Range("C40").Value = dic.Count
    
    k = 0
    
    For Each itm In dic
        'rRngVisible.AutoFilter Field:=6, Criteria1:="5282 01"
        'If Range(AG3 & AG) > 0 & Range(AH3 & AH) > 0 Then k = k + 1
        'k = Application.CountIfs(Range("AG3:AG" & sLastRow), ">0", Range("AH3:AH" & sLastRow), ">0")
        
       If (Sheets("ScrewPoints").Range("AG3:AH" & sLastRow).Value = 0) And _
          (Sheets("ScrewPoints").Range("AH3:AG" & sLastRow).Value = 0) Then
        k = k + 1
        End If
    Next itm
    
    Sheets("Total").Range("C41").Value = k
    k = 0

Thanks

kiran

 

[aRandomHelper]

You have a loop, but you're not exactly looping anything.

Here the looped variable is itm,

For Each itm In dic

But you're always referring to sLastRow no matter how many times you're looping.

(Sheets("ScrewPoints").Range("AG3:AH" & sLastRow).Value = 0)

 

[kirankoushik]

You have a loop, but you're not exactly looping anything.

Here the looped variable is itm,

For Each itm In dic

But you're always referring to sLastRow no matter how many times you're looping.

(Sheets("ScrewPoints").Range("AG3:AH" & sLastRow).Value = 0)

sorry I am a beginner and I am not able to understand..

what I am trying to achieve is..

1. from column 6, i create a dictionary
2. for each value in dictionary (filter for col 6), i am checking column AG and AH have all values as 0
3. if it has all value as 0, i am trying to increment a counter k

could you please correct my code if you dont mind?

 

[aRandomHelper]

1. from column 6, i create a dictionary

I don't understand this line:
dic(rRow.Cells(2, 6).Value) = ""

Are you trying to make a dictionary of the values in column 6?

2. for each value in dictionary (filter for col 6)

Dictionaries have unique Keys; meaning, if there are duplicate values while you look at column 6, only 1 Key is created for all of the duplicates.
If you have 3 rows of "ABC" and 2 rows of "DEF", such as:
1--ABC
2--ABC
3--ABC
4--DEF
5--DEF

Are you going to check columns AG and AH of all 5 rows? or just the first occurrences -> rows 1 (ABC) and 4 (DEF) ?

Before I proceed correcting it I have to understand what you're trying to achieve.

 

[kirankoushik]

I don't understand this line:
dic(rRow.Cells(2, 6).Value) = ""

Are you trying to make a dictionary of the values in column 6?


Dictionaries have unique Keys; meaning, if there are duplicate values while you look at column 6, only 1 Key is created for all of the duplicates.
If you have 3 rows of "ABC" and 2 rows of "DEF", such as:
1--ABC
2--ABC
3--ABC
4--DEF
5--DEF

Are you going to check columns AG and AH of all 5 rows? or just the first occurrences -> rows 1 (ABC) and 4 (DEF) ?

Before I proceed correcting it I have to understand what you're trying to achieve.

Thank you for your time.. really appreciate it..

yes, I am trying to create a make a dictionary from column 6 : i tried to do this as the duplicate values in col 6 are jumbled

yes, I am going to check AG & AH for all rows : but the rows when checked, column 6 should be unique item from dictionary key

this process I am trying to do for all the keys in the dictionary

 

[aRandomHelper]

yes, I am going to check AG & AH for all rows : but the rows when checked, column 6 should be unique item from dictionary key

Using the same example, the 0/1's now represent columns AG/AH
1--ABC----------0-0
2--ABC----------0-1
3--ABC----------0-0
4--DEF----------0-0
5--DEF----------0-1
6--XYZ----------1-0
7--XYZ----------0-1

I see you're counting the unique items, so this should appear as 2 or 3? "ABC" and "DEF", should "XYZ" be counted?
Sheets("Total").Range("C40").Value = dic.Count

With the sample data above, what should be the result for this? 2 or 3?
Sheets("Total").Range("C41").Value = k


edit: added another scenario to sample data

 

[kirankoushik]

Using the same example, the 0/1's now represent columns AG/AH
1--ABC----------0-0
2--ABC----------0-1
3--ABC----------0-0
4--DEF----------0-0
5--DEF----------0-1
6--XYZ----------1-0
7--XYZ----------0-1

I see you're counting the unique items, so this should appear as 2 or 3? "ABC" and "DEF", should "XYZ" be counted?
Sheets("Total").Range("C40").Value = dic.Count

With the sample data above, what should be the result for this? 2 or 3?
Sheets("Total").Range("C41").Value = k


edit: added another scenario to sample data

c40 = 3 (ABC, DEF, XYZ)

c41 = 0 ( coz when ABC all cells in AG & AH are not 0,
coz when DEF all cells in AG & AH are not 0
coz when XYZ all cells in AG & AH are not 0)

 

[aRandomHelper]

Try this amended code:

    dic.CompareMode = vbTextCompare
    With Sheets("ScrewPoints").Range("A2").CurrentRegion
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1)
    End With
   
    For Each rRow In rRngVisible.Rows
        dic(rRow.Cells(1, 6).Value) = Application.Max(dic(rRow.Cells(1, 6).Value), IIf(rRow.Cells(1, "AG").Value = 0 And rRow.Cells(1, "AH").Value = 0, 0, 1))
    Next rRow
   
    Sheets("Total").Range("C40").Value = dic.Count
   
    k = 0
   
    For Each itm In dic
        k = k + IIf(dic(itm) = 0, 1, 0)
    Next itm

    Sheets("Total").Range("C41").Value = k
    k = 0

 

[kirankoushik]

Try this amended code:

    dic.CompareMode = vbTextCompare
    With Sheets("ScrewPoints").Range("A2").CurrentRegion
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1)
    End With
  
    For Each rRow In rRngVisible.Rows
        dic(rRow.Cells(1, 6).Value) = Application.Max(dic(rRow.Cells(1, 6).Value), IIf(rRow.Cells(1, "AG").Value = 0 And rRow.Cells(1, "AH").Value = 0, 0, 1))
    Next rRow
  
    Sheets("Total").Range("C40").Value = dic.Count
  
    k = 0
  
    For Each itm In dic
        k = k + IIf(dic(itm) = 0, 1, 0)
    Next itm

    Sheets("Total").Range("C41").Value = k
    k = 0

???‍

Sorry for the late reply, this code gave me an output of 9 and I had around 1800 lines in my sheet to check it manually., it works perfectly..

Thank you for the gem of a code.. could you please explain how it works ? I need to adapt the same to "C42" where I have same conditions except AG & AH are >0

 

[aRandomHelper]

When I have more time I'll try to break it down and explain.

See if this works as intended:

    dic.CompareMode = vbTextCompare
    With Sheets("ScrewPoints").Range("A2").CurrentRegion
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1)
    End With
   
    For Each rRow In rRngVisible.Rows
        dic(rRow.Cells(1, 6).Value) = Application.Max(dic(rRow.Cells(1, 6).Value), IIf(rRow.Cells(1, "AG").Value = 0 And rRow.Cells(1, "AH").Value = 0, 0, 1))
        dic(rRow.Cells(1, 6).Value & "~C42") = Application.Max(dic(rRow.Cells(1, 6).Value & "~C42"), IIf(rRow.Cells(1, "AG").Value > 0 And rRow.Cells(1, "AH").Value > 0, 0, 1))
    Next rRow
   
    Sheets("Total").Range("C40").Value = dic.Count / 2
   
    k = 0
    kk = 0
   
    For Each itm In dic
        If right(itm, 4) = "~C42" then
            kk = kk + IIf(dic(itm) = 0, 1, 0)
        else
            k = k + IIf(dic(itm) = 0, 1, 0)
        end if
    Next itm

    Sheets("Total").Range("C41").Value = k
    Sheets("Total").Range("C42").Value = kk
    k = 0
    kk = 0