View Full Version : [SOLVED:] VBA Excel - Collections unexpected answer
antoniosm
07-07-2016, 02:30 PM
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
07-07-2016, 05:45 PM
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
07-07-2016, 06:36 PM
Is there a space between Add and Cell
CA.AddCell.Value , Cell.Value
If not, that may be Paul's masked error.
or use:
Sub M_snb()
sheets(1).cells(1).currentregion.columns(1).advancedfilter 2, ,sheets(1).cells(1,3),true
end sub
antoniosm
07-08-2016, 07:30 AM
Thank you very much, will start by doing the Code like suggested!
Thanks
antoniosm
07-08-2016, 07:34 AM
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
07-08-2016, 07:38 AM
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
07-12-2016, 08:50 AM
Thank I'll defiantly will try to develop this type of habits! Hope I can contribute somehow on the future!
mdmackillop
07-12-2016, 09:10 AM
I suggest the habit of using CStr in case any of the data is numerical
CA.Add C.Value, CStr(C.Value)
mikerickson
07-12-2016, 01:41 PM
Or even worse, an error value (e.g #DIV/0). One should never trust what a user might put in a cell.
antoniosm
07-13-2016, 07:51 AM
Thank you very much! Didn't know what was it before.. just learned it!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.