Add condition to this code

Hi,

The following takes from Range("B17:B4516") the unique values avoiding blank cells and displays them in a dropdownlist of a userform:

Dim v, e
With Sheet1.Range("B17:B4516")
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
    If e = "" Then
    Else
        If Not .exists(e) Then .Add e, Nothing
    End If
    Next
    If .Count Then Me.Lista2.List = Application.Transpose(.keys)
End With

What I am looking for but cannot manage to do is to put an extra condition. This condition is to avoid also showing data from Range("B17:B4516") when the value from Range("J17:J4516") is higher than 5.

Any help would be much appreciated.

Kind regards,

How about:

Sub Macro_1()
Dim v
With CreateObject("Scripting.dictionary")
    .comparemode = 1
    For Each v In Sheet1.Range("B17:B4516").Cells
        If Not v = "" And Not Cells(v.Row, 10) > 5 And Not .exists(v) Then .Add v, Nothing
    Next v
    If .Count Then Me.Lista2.List = Application.Transpose(.keys)
End With
End Sub

Sub test1()

Dim v, i&
With ActiveSheet.Range("B17:J4516")
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(v)
      If v(i, 9) > 5 Then
       If v(i, 1) = "" Then
    Else
        If Not .exists(v(i, 1)) Then .Add i, Nothing
       End If
      End If
    Next
    If .Count Then Me.Lista2.List = Application.Transpose(.keys)
End With

End Sub

Hi!

I tried both codes and its almost working! Thanks for this help.

@yudlugar, yours works perfect the only thing is that its not displaying the unique values, the part .exists(v) is the one that is not going...

@AB33, yours is also working but for some reason it displays more values than the ones I have... something strange... I think its something related to

For i = 1 To UBound(v)

That maybe is taking into account more data...

Thanks once again for helping on this.

Regards,

Not,
Your range is now extended to column J, but testing the same column which is column B.

For i = 1 To UBound(v)

is looping down through rows which is the same as you had

 For Each e In v

.
All I have done is convert the range in to an array which is much faster than looping through each range.
I have not change anything except adding another condition, which now tests column J while your last code only tests column B.
If you look my range is not longer the same as the old one

With ActiveSheet.Range("B17:J4516")
    v = .Value
End With

One of these codes work for you, but you need to change the OR and AND. I am not sure which is right for you.
My preference is the last(3rd) code with swapping the OR and AND if necessary

Sub test1()

Dim e As Range

With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In ActiveSheet.Range("B17:B4516")
    If e = "" Or e.Offset(, 8) Then
    Else
        If Not .exists(e) Then .Add e, Nothing
    End If
    Next
    If .Count Then Me.Lista2.List = Application.Transpose(.keys)
End With
End Sub

Sub test11()

Dim v, i&
With ActiveSheet.Range("B17:J4516")
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(v)
      If v(i, 9) > 5 Or v(i, 1) = "" Then
    
    Else
        If Not .exists(v(i, 1)) Then .Add i, Nothing
       End If
    Next
    If .Count Then Me.Lista2.List = Application.Transpose(.keys)
End With

End Sub

Sub test111()

Dim v, i&
With ActiveSheet.Range("B17:J4516")
    v = .Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For i = 1 To UBound(v)
      If Len(v(i, 1)) Or v(i, 9) < 5 Then
        If Not .exists(v(i, 1)) Then .Item(v(i, 1)) = Empty
        End If
    Next
    If .Count Then Me.Lista2.List = Application.Transpose(.keys)
End With

End Sub

For my code I guess you could try changing .exists(v) to .exists(v.value) but not sure if it would make a difference. Can't see why else it wouldn't work.

Hello!!

Sorry I have been out for a few days... They are both working great!
I guess I was doing something wrong before...

I really appreciate your help!!

Thankss!