VBA Excel - Collections unexpected answer

[antoniosm]

Hi, first time posting and a beginner in Excel so probably not asking a very interesting question (sorry).

I'm trying to develop a code and having some issues on the "collections".

In the code bellow on the range selected for the collection CA I have several duplicates and it always returns 0 on the count.

(Already checked that the range is correct)

Can anyone identify anything wrong with this code?

Thank you very much

Sub report()
Dim RName AsString
Dim R1 AsLong
Dim CA As New Collection
Dim i AsLong


' Select Range for view duplicates


R1 = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & R1).Select




' Add Values to Collection A


OnErrorResumeNext
For Each Cell In Range("A2:A" & R1)
CA.AddCell.Value , Cell.Value
Next Cell
OnErrorGoTo0


MsgBox "Unique Count : " & CA.Count


' Print Unique Values from Collection A


For i = 1 To CA.Count
Cells(i, "C") = CA.Item(i)
Next


' Create new Worksheet with Today's date and Hour


RName = Format(Date, "yyyy_mm_dd") & "_" & Format(Now(), "h_Hmm") & "_Report"
Sheets.Add.Name = RName
Worksheets(RName).Delete


EndSub

[Paul_Hossler]

You can use the [#] icon to add [ CODE ] .... [/ CODE ] tags and paste your code between them to have better formatting

It was a little hard to see if the On Error Resume Next was covering up a format error since a lot of the statements in your macro got joined: CA.AddCell.Value , Cell.Value

I Dim-ed C and used it in the loop and this seems to work in my test


What I think / guess was happening was that nothing was ever added to the collection since there was a syntax or other error that was bypassed because of the On Error Resume Next, making the count = 0




Option Explicit
Sub report()
Dim RName As String
Dim R1 As Long
Dim CA As New Collection
Dim i As Long
Dim C As Range '---------------------------

' Select Range for view duplicates
R1 = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & R1).Select


' Add Values to Collection A
On Error Resume Next
For Each C In Range("A2:A" & R1).Cells '---------------------------
CA.Add C.Value, C.Value '---------------------------
Next C '---------------------------
On Error GoTo 0

MsgBox "Unique Count : " & CA.Count

' Print Unique Values from Collection A
For i = 1 To CA.Count
Cells(i, "C") = CA.Item(i)
Next

' Create new Worksheet with Today's date and Hour
'RName = Format(Date, "yyyy_mm_dd") & "_" & Format(Now(), "h_Hmm") & "_Report"
'Sheets.Add.Name = RName
'Worksheets(RName).Delete
End Sub

[mikerickson]

Is there a space between Add and Cell


CA.AddCell.Value , Cell.Value

If not, that may be Paul's masked error.

[snb]

or use:


Sub M_snb()
sheets(1).cells(1).currentregion.columns(1).advancedfilter 2, ,sheets(1).cells(1,3),true
end sub

[antoniosm]

Thank you very much, will start by doing the Code like suggested!
Thanks

[antoniosm]

Thank you all! This is absolutely great

Mikerickson "unfortunately" was exactly that! So much time doing tests to find the error (maybe because I'm working on Mac it's harder to spot this as it doesn't have the suggestions etc..)

Snb thanks also, is that a filter for duplicates? The end idea is to create a report on another sheet with the individual values and do a series of sumif and countif etc... I have to create a very easy way to create this report automatically to someone that doesn't understand about pivot tables.

Thanks again

[mikerickson]

I work on a Mac and don't find it hard to debug.

One habit that I've developed is to use named arguments, like

CA.Add item:=Cell.Value, key:=Cell.Value

Another habit is that when I use On Error Resume Next, I do my intial testing with that line commented out and non-error causing data. When I'm satisfied with the code, then I'll comment the line in and test the error causing (and ignoring) situations.

[antoniosm]

Thank I'll defiantly will try to develop this type of habits! Hope I can contribute somehow on the future!

[mdmackillop]

I suggest the habit of using CStr in case any of the data is numerical

CA.Add C.Value, CStr(C.Value)

[mikerickson]

Or even worse, an error value (e.g #DIV/0). One should never trust what a user might put in a cell.

[antoniosm]

Thank you very much! Didn't know what was it before.. just learned it!