WorksheetFunction.countif criteria not working

[mecerrato]

I am using the following code to create an email list of unique emails. The list has a lot of duplicates, but I only want them once. Some of the rows have not been assigned an email so they show as "UNASSIGNED" <unassigned>and I want to ignore those.


I have used this in another sheet where it is working well, the difference is that on this new application I need to copy the data to a temporary location, because it is filtered and the CountIf does not work on filtered rows.


The code is suppose to ignore the criteria for the text "UNASSIGNED"<unassigned>.


I first use the CountIf to get the loan officer email list (MLO list). That works fine I then have code to get the Processor list that has some rows with word "UNASSIGNED", this is not working as it should. The code below Processor list is supposed to ignore any value that equals "UNASSIGNED"<unassigned>, but it doesn't:

Sheets(2).Cells.ClearContents
  lastSrcRw = Sheets("Pipeline").Cells(Rows.Count, 2).End(xlUp).Row
     For Each cell In Sheets("Pipeline").Range("E11:E" & lastSrcRw).SpecialCells(xlCellTypeVisible)
        dstRw = dstRw + 1
        cell.Copy Sheets(2).Range("A" & dstRw)
     Next


'Loop through Sheet2 list, extract unique addresses
  lastTmpRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
     For tmpRw = 1 To lastTmpRw
         If WorksheetFunction.CountIf(Sheets(2).Range("A1:A" & tmpRw), _
            Sheets(2).Range("A" & tmpRw)) < 2 Then
               addylist_tmp = addylist_tmp & Sheets(2).Range("A" & tmpRw).Value & "; "
         End If
     Next tmpRw


'Clean up temp addylist
     addylist = Left(addylist_tmp, Len(addylist_tmp) - 2)
     'MsgBox addylist


'Processor List
Sheets(2).Cells.ClearContents
  lastSrcRw = Sheets("Pipeline").Cells(Rows.Count, 4).End(xlUp).Row
     For Each cell In Sheets("Pipeline").Range("C11:C" & lastSrcRw).SpecialCells(xlCellTypeVisible)
        dstRw = dstRw + 1
        cell.Copy Sheets(2).Range("D" & dstRw)
     Next


'Loop through Sheet2 list, extract unique addresses
  lastTmpRw = Sheets(2).Cells(Rows.Count, 4).End(xlUp).Row
     For tmpRw = 1 To lastTmpRw
         If WorksheetFunction.CountIf(Sheets(2).Range("D1:D" & tmpRw), "<>" & "UNASSIGNED<unassigned>") Then
         If WorksheetFunction.CountIf(Sheets(2).Range("D1:D" & tmpRw), Sheets(2).Range("D" & tmpRw)) < 2 Then
                addylist_tmp2 = addylist_tmp2 & Sheets(2).Range("D" & tmpRw).Value & "; "
         End If
         End If
     Next tmpRw


'Clean up temp addylist
     addylist2 = Left(addylist_tmp2, Len(addylist_tmp2) - 2)

</unassigned></unassigned></unassigned></unassigned>

 

[Fluff]

How about

Sub mecerrato()
   Dim Cl As Range
   Dim Mlst As String, Plst As String
   Dim Mdic As Object, Pdic As Object
   
   Set Mdic = CreateObject("scripting.dictionary")
   Set Pdic = CreateObject("scripting.dictionary")
   With Sheets("Pipeline")
      For Each Cl In .Range("C11", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         If Cl.Value <> "" And Cl.Value <> "UNASSIGNED" Then Pdic(Cl.Value) = Empty
         If Cl.Offset(, 2).Value <> "" Then Mdic(Cl.Offset(, 2).Value) = Empty
      Next Cl
   End With
   Mlst = Join(Mdic.Keys, "; ")
   Plst = Join(Pdic.Keys, "; ")
End Sub

 

[mecerrato]

This worked perfectly for M1st but P1st is blank, it should pick up each value once but ignore any that say UNASSIGNED. I am using MsgBox to show the variables and P1st is always blank, it should show all the other values.

On another note, I never tried this method (I like it), this works on filtered lists as well?
I mean it does but I am trying to follow the code and don't see where it is accounting for a filtered list.
In the code it says what to do if the field is empty but how does it know to add it if it isn't? Like I feel there should be an ELSE. Don't get me wrong it works fine I was just curious:

If Cl.Offset(, 2).Value <> "" Then Mdic(Cl.Offset(, 2).Value) = Empty

 

[Fluff]

Run this & what does the msgbox say

Sub mecerrato()
   Dim Cl As Range
   Dim Mlst As String, Plst As String
   Dim Mdic As Object, Pdic As Object
   
   Set Mdic = CreateObject("scripting.dictionary")
   Set Pdic = CreateObject("scripting.dictionary")
   With Sheets("pcode")
      For Each Cl In .Range("C11", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         If Cl.Value <> "" And Cl.Value <> "UNASSIGNED" Then Pdic(Cl.Value) = Empty
         If Cl.Offset(, 2).Value <> "" Then Mdic(Cl.Offset(, 2).Value) = Empty
      Next Cl
   End With
   MsgBox "Mdic " & Mdic.Count & vbLf & "Pdic " & Pdic.Count
   Mlst = Join(Mdic.Keys, "; ")
   Plst = Join(Pdic.Keys, "; ")
End Sub

 

[mecerrato]

Results:
Mdic 1
Pdic 2

 

[mecerrato]

It is odd because when I step through it I stop right before the MsgBox code and I hover over the variable and all the processors are listed in the P1st variable but the MsgBox shows nothing. The M1st shows all of them correctly.

 

[Fluff]

Results:
Mdic 1
Pdic 2

This means that you only have 1 unique value for the Mlst & 2 for the Plst
Is that correct?

It is odd because when I step through it I stop right before the MsgBox code and I hover over the variable and all the processors are listed in the P1st variable but the MsgBox shows nothing. The M1st shows all of them correctly.

I don't understand what you mean by this as both Mlst & Plst will be empty when the msgbox shows.

 

[mecerrato]

I put a stopper in the code at the MsgBox M1st line just so I can hover over the variables to see their values. The M1st correctly shows all the values but the P1st is empty. M1st should contain all the names in the field once with the exception of the <unassigned><unassigned><unassigned>UNASSIGNED <unassigned" <unassigned="">value.</unassigned"></unassigned></unassigned></unassigned>

 

[Fluff]

In that case can you please post the code you are using, because the with the code I supplied neither Mlst, nor Plst will have a value until after the msgbox

 

[mecerrato]

Here is the code I am using, the MsgBox for M1st shows all the values the MsgBox for P1st shows nothing:

Sub mecerrato1()   Dim Cl As Range
   Dim Mlst As String, Plst As String
   Dim Mdic As Object, Pdic As Object
   
   Set Mdic = CreateObject("scripting.dictionary")
   Set Pdic = CreateObject("scripting.dictionary")
   With Sheets("Pipeline")
      For Each Cl In .Range("C11", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         If Cl.Value <> "" And Cl.Value <> "UNASSIGNED" Then Pdic(Cl.Value) = Empty
         If Cl.Offset(, 2).Value <> "" Then Mdic(Cl.Offset(, 2).Value) = Empty
      Next Cl
   End With
   Mlst = Join(Mdic.Keys, "; ")
   Plst = Join(Pdic.Keys, "; ")
   MsgBox Mlst
   MsgBox P1st
End Sub