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]

AutoFilter Field:=9, Criteria1:="Final" while setting the range so that any calculating taking place is already defined by visible rows after auto filter..

Can you show your code that includes this filtering portion?

When I manually filter to "Final" on that data sample, I get 3 OK and 34 NOK with the ".SpecialCells(xlcelltypevisible)" added.

 

[kirankoushik]

Can you show your code that includes this filtering portion?

When I manually filter to "Final" on that data sample, I get 3 OK and 34 NOK with the ".SpecialCells(xlcelltypevisible)" added.

i used the below code..

 Set dic = CreateObject("scripting.dictionary")
    dic.CompareMode = vbTextCompare
    Set rng1 = Sheets("Screwpoints").Range("A2").CurrentRegion
    rng1.AutoFilter Field:=29, Criteria1:="Final", Operator:=xlFilterValues
    With Sheets("Screwpoints").Range("A2").CurrentRegion
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    End With

 

[aRandomHelper]

You don't need another range variable to do the filtering, you can combine like this:

    Set dic = CreateObject("scripting.dictionary")
    dic.CompareMode = vbTextCompare
    With Sheets("Screwpoints").Range("A2").CurrentRegion
        .AutoFilter Field:=9, Criteria1:="Final", Operator:=xlFilterValues '<--  Just make sure the Field:=9 is pointing to the right column -- is it 9 or 29?
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    End With

 

[kirankoushik]

You don't need another range variable to do the filtering, you can combine like this:

    Set dic = CreateObject("scripting.dictionary")
    dic.CompareMode = vbTextCompare
    With Sheets("Screwpoints").Range("A2").CurrentRegion
        .AutoFilter Field:=9, Criteria1:="Final", Operator:=xlFilterValues '<--  Just make sure the Field:=9 is pointing to the right column -- is it 9 or 29?
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    End With

i get 3 and 71 still.,

 

[aRandomHelper]

Can you add this line and see what is printed in the immediate window in VBA editor?

    With Sheets("Screwpoints").Range("A2").CurrentRegion
        .AutoFilter Field:=9, Criteria1:="Final", Operator:=xlFilterValues
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        Debug.Print rRngVisible.Address '<--
    End With

 

[kirankoushik]

Can you add this line and see what is printed in the immediate window in VBA editor?

    With Sheets("Screwpoints").Range("A2").CurrentRegion
        .AutoFilter Field:=9, Criteria1:="Final", Operator:=xlFilterValues
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        Debug.Print rRngVisible.Address '<--
    End With

i got below error

 

[aRandomHelper]

i got below error

How did you get it to run previously?

Add this line too:

    With Sheets("Screwpoints").Range("A2").CurrentRegion
        Debug.Print .Address '<--
        .AutoFilter Field:=9, Criteria1:="Final", Operator:=xlFilterValues
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        Debug.Print rRngVisible.Address '<--
    End With

 

[kirankoushik]

How did you get it to run previously?

Add this line too:

    With Sheets("Screwpoints").Range("A2").CurrentRegion
        Debug.Print .Address '<--
        .AutoFilter Field:=9, Criteria1:="Final", Operator:=xlFilterValues
        Set rRngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        Debug.Print rRngVisible.Address '<--
    End With

I'm stumped.. rebooted system, cleared %temp% & recent.. still getting same error.. earlier it ran without any errors!

 

[aRandomHelper]

Press Ctrl+G in VBA editor, what is shown there? It should have printed the first address.

 

[kirankoushik]

Press Ctrl+G in VBA editor, what is shown there? It should have printed the first address.

Edit :

it prints one line each time I run it