Find as a counter

[interner]

I am trying to compare two columns in two separate worksheets in the same book. Worksheet 13 has two columns, columns O and P with serial numbers as does worksheet 3. What I am trying to do is if there is a serial number in worksheet 13 that also appears in worksheet 3, I want to count each time this occurrence happens. Here is my code so far. The problem I am having is that it is simply counting how many serial numbers there are and I can't use an Is Not Nothing statement. Any help would be greatly appreciated, this is driving me up a wall.

 Sub countdups()

    Dim UsedRowsMaster As Long
    Dim UsedRowsNew As Long

    Dim Master As Workbook
    Dim Update As Workbook
    Dim ShopOrder As String
    Dim Service As String
    Dim Ustring As String

    UsedRowsUpdate = Worksheets(13).UsedRange.Rows.Count
    'Search and match
    For Each cell1 In Worksheets(13).Range("O" & "2:" & "P" & UsedRowsUpdate)
        Set cell2 = Worksheets(3).Columns("O").Cells _
        .Find(What:=cell1.Value, After:=Range("O1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        If cell2 Is Nothing Then
            GoTo Label1:
            Else
            counterdup = counterdup + 1
        End If
   
Label1:
    Next cell1
    MsgBox counterdup
End Sub

 

[wigi]

So, this is actually the COUNTIF function you are going to write again in VBA? Or am I missing something essential here?

 

[interner]

Now that I think about it, yes I am reinventing the wheel of the countif. How would I do the countif on sheet 3 respective of the values in each cell in sheet 13...

 

[wigi]

Now that I think about it, yes I am reinventing the wheel of the countif. How would I do the countif on sheet 3 respective of the values in each cell in sheet 13...

Euhm... you create the COUNTIF in 1 cell, and copy that cell down the column, for as many cells as needed.

 

[interner]

I should clarify that it needs to be in VBA. More clarified,

For each cell in columns O and P of worksheet 13 I want to take each value then search and see if it is also located in columns O and P of worksheet 3. If it is found, then I want 'count = count + 1'. I'm sorry if I am being too vague.

 

[interner]

I think I might have it

Sub countdups()

    Dim UsedRowsMaster As Long
    Dim UsedRowsNew As Long

    Dim Master As Workbook
    Dim Update As Workbook
    Dim ShopOrder As String
    Dim Service As String
    Dim Ustring As String
    UsedRows = Worksheets(13).UsedRange.Rows.Count + 500
    For Each cell1 In Worksheets(13).Range("O" & "2:" & "P" & UsedRows)
If Application.WorksheetFunction.CountIf(Worksheets(3).Range("O2:P" & UsedRows), cell1.Value) > 0 Then
truecount = truecount + 1
End If
Label1:
    Next cell1
    MsgBox truecount
    
End Sub

 

[wigi]

Sub countdups()

    UsedRowsUpdate = Sheets(13).UsedRange.Rows.Count
    
    'Search and match
    For Each cell In Sheets(13).Range("O2:P" & UsedRowsUpdate)

        counterdup = counterdup - (WorksheetFunction.CountIf(Sheets(3).Columns("O"), cell.Value) > 0)

    Next

    MsgBox counterdup

End Sub

 

[interner]

thanks wigi, plus a few tweaks that is exactly what I needed!