Bdtjtk

I have data in a column as follows:

8856

8867

8876

8856

8898

My objective is to compare each cell of the column and if the values are the same to execute an if statement.

Private Sub CommandButton2_Click()



Dim sht1 As Worksheet

Dim sht2 As Worksheet

Dim C1row As Long

Dim C2row As Long

Dim C2TotalRows As Long

Dim CustID As String



Set sht1 = Worksheets("Report")



sht1.Activate

C2TotalRows = Application.CountA(Range("A:A"))

C1row = 2



Do While sht1.Cells(C1row, 3).Value <> ""



    CustID = sht1.Cells(C1row, 3).Value



    For C2row = 2 To C2TotalRows



        If CustID = Cells(C2row, 3).Value Then

            MsgBox CustID

            Exit For

        End If



    Next

    C1row = C1row + 1



Loop

End Sub

Just assumption, I see that C2TotalRows=C2TotalRows = Application.CountA(Range("A:A")), which is equal to 1, because of range "A:A". So, For C2row = 2 To C2TotalRows this loop will never run. Try specify another cells range.

It seems that you would like to know how often a CustID (like 8856) occurs in the data and at which row number.
I created a simple class cInfo for that and then I put the information together in a dictionary. At the end I just printed the information but you could add the code you would like to run

Here is the class cInfo

Option Explicit



Public rowNr As String

Public ocur As Long

And that's the code to collect the information

Sub UniqueValues()

Dim dict As Scripting.Dictionary

Dim rg As Range, sngCell As Range

Dim i As Long

Dim lRow As Long

Dim cellInfo As cInfo



    lRow = Range("A1").End(xlDown).Row      'Assumption now free rows and at least on entry in row 2

    Set rg = Range("A2:A" & lRow)

    Set dict = New Dictionary



    For Each sngCell In rg

        If dict.Exists(sngCell.Value) Then

            dict.Item(sngCell.Value).ocur = dict.Item(sngCell.Value).ocur + 1

            dict.Item(sngCell.Value).rowNr = dict.Item(sngCell.Value).rowNr & ";" & CStr(sngCell.Row)

        Else

            Set cellInfo = New cInfo

            cellInfo.rowNr = CStr(sngCell.Row)

            cellInfo.ocur = 1

            dict.Add sngCell.Value, cellInfo

        End If

    Next



    ' Do sth here. I will print some info

    For i = 0 To dict.Count - 1

        Debug.Print "CustID:", dict.Keys(i), dict.Items(i).ocur, "occurence(s) in rows", dict.Items(i).rowNr

    Next



End Sub

This worked fine with the example data you provided

Output

Try this modified your code:

Private Sub CommandButton2_Click()

  Dim sht1 As Worksheet

  Dim C1row As Long

  Dim CustID As String

  Dim R As Range



  Set sht1 = Worksheets("Report")

  sht1.Activate

  C1row = 2



  Do While sht1.Cells(C1row, 3).Value <> ""

    CustID = sht1.Cells(C1row, 3).Value

    Set R = sht1.Range("C:C").Find(CustID, sht1.Cells(C1row, 3))

    If R.Row > C1row Then

      MsgBox CustID

    End If

    C1row = C1row + 1

  Loop

End Sub

Good luck and thank you.

Reference: Range.Find method (Excel)