0

I am trying to loop through my worksheet to count any duplicates. UniqueId is a collection of ranges. 

Dim uniqueId As New Collection
For i = 1 To numSets
    Dim rng As Range

    'Find Unique Id Range
    Set rng = Application.InputBox(("In Data Set " & i & ", please select range of the unique identifier. (Do not include header)"), "Obtain Unique ID Column", Type:=8)
    uniqueId.Add rng
Next

 For i = 1 To numSets
    For j = 2 To lastcell
       If (Application.WorksheetFunction.CountIf(Range(uniqueId(i).Address), Cells(j, 1).Value) > 1) Then
            If (Cells(j, numSets * numVar + numVar + 3).Value <> "") Then
                Cells(j, numSets * numVar + numVar + 3).Value = Cells(j, numSets * numVar + numVar + 3).Value & ", Review- this is a duplicate in Data Set " & i
            Else
                Cells(j, numSets * numVar + numVar + 3).Value = "Review- this is a duplicate in Data Set " & i
            End If
        End If
    Next
Next

The count if is not working because it should have duplicates but it never does. I think the problem may come down to Range(uniqueId(i).Address) but I am not sure.

Rebecca Gonzalez
  • 53
  • 5
  • 1
    Can't reproduce the problem without the data – nicomp Jul 09 '18 at 13:49
  • [WorksheetFunction.CountIf](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheetfunction-countif-method-excel) needs a range for it's first parameter, not the string address of the range. Just do `CountIf(uniqueId(i), Cells(j, 1).value)` and see if that clears it up. – JNevill Jul 09 '18 at 13:52
  • Need to know value of uniqueId(i).Address at time of bad count. –  Jul 09 '18 at 13:53
  • @JNevill `Range(uniqueId(i).Address)` is a Range. It is a roundabout way of saying `uniqueId(i)`, but it is a range. – GSerg Jul 09 '18 at 13:53
  • @GSerg [Range.Address](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-address-property-excel) returns a string, not a range. – JNevill Jul 09 '18 at 13:54
  • Data Set 1 is a just a list of integers (1-20), so is data set 2, so that should be whats going into uniqueId. There are duplicate integers. Cells(j,1) will have each integer 1-20 just once. numVar is just a variable to help me with the placement of the comments in the code. – Rebecca Gonzalez Jul 09 '18 at 13:54
  • @JNevill Yes, and then `Range(Range.Address)` turns it back to a range. – GSerg Jul 09 '18 at 13:54
  • 1
    @GSerg Oh. I see what you are saying. That is definitely roundabout. That would cause all sorts of problems if a different sheet was in the collection though. Ack. – JNevill Jul 09 '18 at 13:55
  • @JNevill Problem Fixed with uniqueId(i). Thank you!! Not sure why cause the roundabout way was used elsewhere in the code and works great. – Rebecca Gonzalez Jul 09 '18 at 13:56
  • @RebeccaGonzalez If that fixed it for you, that means you have been changing active sheets. `Range(range.address)` actually means "range on the current sheet with the address matching the address of the given range (that can be from a different sheet)". You should [qualify your `Range`s and `Cells`](https://stackoverflow.com/q/17733541/11683) at all times. – GSerg Jul 09 '18 at 14:00
  • @RebeccaGonzalez "roundabout way" involving unqualified `Range` calls is inevitably going to run into problems. Unqualified `Range` (or `Cells`, or `Columns`, `Rows`, or `Names`) calls are implicitly referring to `ActiveSheet`, so if `ActiveSheet` isn't the same sheet your range is on, things tend to blow up. – Mathieu Guindon Jul 09 '18 at 14:00

0 Answers0