NoDupes for numbers in just one cell using split

[drom]

Hi and Thanks in advance!

I am using many times the following lines of code:

Dim cNoDupes As New Collection
  Dim aCell():                aCell() = Selection.Value
Dim xAAA As Long:             xAAA = LBound(aCell())
Dim xZZZ As Long:             xZZZ = UBound(aCell())
Dim X As Long
Dim wTemp As String

    For X = xAAA To xZZZ
      wTemp = "":             wTemp = aCell(X)
      If wTemp <> "" Then
        cNoDupes.Add wTemp, CStr(wTemp)
      End If
    Next X
      Debug.Print cNoDupes.Count

works fine when I have text values in my selection

Now I would like to use a similar code for number, king of:

The prior code is the one I have been using for a range of cells

Now I am using just one cell, so I am trynf to use:

Sub MyCodeForMyQuery()

  Dim aCell()
Dim cNoDupes As New Collection
  Dim wTemp As String
  Dim wCell As String:          wCell = "3,14,14,aa,aa,,aa,,,14,39,40,0, 0,0,a,aa,a,5,5,5,9,tt,t,45yyt" 'ActiveCell, for this eg enough
Dim vCell As Variant:           vCell = Split(wCell, ",")
  Dim xZbat As Long:            xZbat = UBound(vCell)
Dim xAAA As Single:             xAAA = LBound(vCell)
Dim xZZZ As Single:             xZZZ = UBound(vCell)
Dim xTemp As Single
Dim X As Single, XX As Single
                                If wCell = "" Or xZZZ = 0 Then Exit Sub
  
  For X = LBound(vCell) To UBound(vCell)
    wTemp = "":                 wTemp = vCell(X)
    xTemp = 0:                  xTemp = CSng(wTemp)
    If wTemp <> "" Then
      If xTemp > 0 Then
        XX = XX + 1:            ReDim Preserve aCell(1 To XX)
                                aCell(XX) = xTemp
        'Here is where I have Problems:
          cNoDupes.Add xTemp, CSng(xTemp)
      End If
    End If
  Next X



      Debug.Print cNoDupes.Count

End Sub

 

[Alex Blakenburg]

How are you getting it to work without an On Error line ?
Also xTemp = CSng(wTemp) errors out when you pass it a Text value such as "aa".
This "cNoDupes.Add xTemp, CSng(xTemp)" has the Key as a numeric value it needs it to be a String

Give it a try replacing your for / next section with this:

  For X = LBound(vCell) To UBound(vCell)
    wTemp = "":                 wTemp = vCell(X)
    xTemp = 0
    If wTemp <> "" And IsNumeric(wTemp) Then
      xTemp = CSng(wTemp)
      If xTemp > 0 Then
        XX = XX + 1:            ReDim Preserve aCell(1 To XX)
                                aCell(XX) = xTemp
        'Here is where I have Problems:
        On Error Resume Next
          cNoDupes.Add xTemp, CStr(xTemp)
        On Error GoTo 0
      End If
    End If
  Next X

 

[drom]

I have forgotten to add on my macro:
I usually put On error resume next
The code was just fot this eg

I thought maeby was possible to add non string to a collection as non string

 

[Alex Blakenburg]

I thought maeby was possible to add non string to a collection as non string

As an Item yes but not as a Key.
Did you try what I gave you ?