using array to find unique values in a column

[mike08]

Hi there,

I started to study array in vb. After the basics, i applied my array logic to print unique column values under a column.
But somthing wrong with my code.The code is

Private Sub CommandButton1_Click()
With Sheets("Purchase")
  i = 2
  k = 1
  x = 0
 Do While (i <= 9)  'read each cell value
     s = Sheets("Purchase").Cells(i, 2)
     farray(x) = s    ' store each value in an array
     ' msgbox "Cell values in array " & farray(x)
     i = i + 1
     x = x + 1
     msgbox "X= " & x
Loop
x = x - 1
'Compare each item in array with all the remaining items in that array

For temp = 0 To x
   test = 0
   For j = temp + 1 To x
        If farray(temp) = farray(j) Then
            test = 1
        End If
   Next j
   If test = 0 Then
       farray2(k) = farray(temp)
       msgbox "Farray2(k)=" & farray2(k)
       k = k + 1
   End If
 Next temp
 msgbox "K=" & k
'to print array
For m = 1 To k
   msgbox farray2(m)
Next
End With
End Sub

I am not getting what is wrong in the code. Basically i have very limitted experience with vb. So your help would be greatly appreciate.
In sheet("Purchase"), i have the following column FA, is in second column.
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>FA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SBPU017</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mail002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mail003</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mail003</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mail003</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mail003</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mail002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mail002</TD></TR></TBODY></TABLE>

 

[xenou]

It's not easy for me to follow your code. Or rather I'm too lazy at this time. It's always best to step through your code in order to do your debugging...you can watch the code execute line by line and really see what's happening.
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

Below is another example. Even here its not how I would actually do it but maybe you'd find it interesting to see another attempt. (I have a function in my "toolbox" that just strips duplicates out of an array so in reality I'd pass the array with all the values to my function...I guess that's the advantage of having been doing this for the last two years.)

Sub Test()
Dim i As Long
Dim j As Long
Dim a() As Variant
Dim blnFound As Boolean
Dim LRow As Long
Dim r As Range
Dim ws As Worksheet

Set ws = ActiveSheet

[COLOR="SeaGreen"]'//Get a range reference (r)  --  cells to search in[/COLOR]
With ws
    LRow = .Cells(Rows.Count, "AF").End(xlUp).Row
    Set r = .Range(.Cells(1, "AF"), .Cells(LRow, "AF"))
End With

[COLOR="seagreen"]'//Seed first value in array[/COLOR]
ReDim a(1 To 1)
a(1) = r.Cells(1)

[COLOR="seagreen"]'//Loop cells (outer loop)[/COLOR]
For i = 1 To r.Cells.Count
    
[COLOR="seagreen"]    '//Loop array (inner loop)[/COLOR]
    blnFound = False [COLOR="seagreen"]'//Prime boolean variable[/COLOR]
    For j = 1 To UBound(a)
        If a(j) = r.Cells(i).Value Then
            blnFound = True [COLOR="seagreen"]'//Value already exists in this array[/COLOR]
            Exit For[COLOR="SeaGreen"] '//allow for early exit to speed execution[/COLOR]
        End If
    Next j
    
    If Not blnFound Then [COLOR="seagreen"]'//Add to array - value was not found in array already[/COLOR]
        ReDim Preserve a(1 To UBound(a) + 1) [COLOR="seagreen"]'//Expand array by one[/COLOR]
        a(UBound(a)) = r.Cells(i).Value
    End If

Next i

[COLOR="seagreen"]'//report results under last row in column[/COLOR]
ws.Cells(LRow + 1, "AF").Resize(UBound(a), 1).Value = WorksheetFunction.Transpose(a)
[COLOR="seagreen"]'//Highlight our work[/COLOR]
ws.Cells(LRow + 1, "AF").Resize(UBound(a), 1).Interior.ColorIndex = 6

End Sub

 

[jindon]

mike08

Good try!

1) I don't see ReDim statement for farray, farray2, does the code compile ?

2) It will be quicker to exit the loop when the dups is found

    For j = temp + 1 To x
        If farray(temp) = farray(j) Then
            test = 1
            Exit For
        End If
   Next j

<SCRIPT type=text/javascript> vbmenu_register("postmenu_1814196", true); </SCRIPT>

 

[jindon]

mike08

I just realised that your second loop
For temp + 1 To x
should need to start from 0

    For j = 0 To x
        If temp <> j And farray(temp) = farray(j) Then
            test = 1
            Exit For
        End If
   Next j

 

[mike08]

thanks a lot for all help, i realized my problem & now solved.