Pull results for unique values in 2 columns on separate worksheets

[DThib]

I have this working if both columns are on the same worksheet, but I need it to work with 2 different worksheets in the same workbook.

Help:

Sub UniqueLooks()


    Dim rngCell As Range
    Dim r1 As Range
    Dim r2 As Range
    
    Set r1 = Worksheets("Res").Range("A1", Range("A1").End(xlDown))
[B][COLOR=#b22222]    Set r2 = Worksheets("Soup").Range("B2", Range("B2").End(xlDown))[/COLOR][/B]
    
    For Each rngCell In r1


        If WorksheetFunction.CountIf(r2, rngCell) = 0 Then
            sheets("Res").Range("C" & Rows.Count).End(xlUp).Offset(1) = rngCell
        End If
    Next


End Sub

It throws an error when it is pointing at 2 worksheets at the red text

 

[Marcelo Branco]

Try (observe the dots in red)

Sub UniqueLooks()
    Dim rngCell As Range
    Dim r1 As Range
    Dim r2 As Range
    
    With Worksheets("Res")
        Set r1 = [B][COLOR=#ff0000].[/COLOR][/B]Range("A1", [B][COLOR=#ff0000].[/COLOR][/B]Range("A1").End(xlDown))
    End With
    
    With Worksheets("Soup")
        Set r2 = [B][COLOR=#ff0000].[/COLOR][/B]Range("B2", [B][COLOR=#ff0000].[/COLOR][/B]Range("B2").End(xlDown))
    End With
    
    For Each rngCell In r1
        If WorksheetFunction.CountIf(r2, rngCell) = 0 Then
            Sheets("Res").Range("C" & Rows.Count).End(xlUp).Offset(1) = rngCell
        End If
    Next
End Sub

M.

 

[DThib]

Genius!

Thanks for the assist.

DThib

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[DThib]

Also,

This code is part of a larger draw. If the unique id is found, continue pulling data for that row.
When I place this in the code stream, it iterates it continuously instead of finding the unique id and finishing the data dump and going to the next result. Here is the entire stream.

I am not sure why it does not work:

Sub Workie1()


  Dim LastRow, SecondRow As Long
  Dim i As Long
  Dim j As Long, BatchP As Long
  Dim Sp As Variant, X As Variant
  Dim loop_ctr As Integer
  Dim rngCell, r1, r2 As Range


    With Worksheets("MK_DB1")
        LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
    End With
    With Worksheets("SPR")
        SecondRow = .Cells(Rows.Count, "A").End(xlUp).Row
    End With
    
        i = 1 + LastRow
        j = 1 + SecondRow
    For i = 1 To LastRow 'Each i In Worksheets("MK_DB1")
        If Worksheets("MK_DB1").Cells(i, 22) = "Knapczyk, Maciej" Then
           
[FONT=comic sans ms][B][COLOR=#b22222]           With Worksheets("SPR")[/COLOR][/B][/FONT]
[FONT=comic sans ms][B][COLOR=#b22222]               Set r1 = .Range("A1", .Range("A2").End(xlDown))[/COLOR][/B][/FONT]
[FONT=comic sans ms][B][COLOR=#b22222]           End With[/COLOR][/B][/FONT]
[FONT=comic sans ms][B][COLOR=#b22222]           With Worksheets("MK_DB1")[/COLOR][/B][/FONT]
[FONT=comic sans ms][B][COLOR=#b22222]               Set r2 = .Range("B2", .Range("B2").End(xlDown))[/COLOR][/B][/FONT]
[FONT=comic sans ms][B][COLOR=#b22222]           End With[/COLOR][/B][/FONT]

[FONT=comic sans ms][B][COLOR=#b22222]            For Each rngCell In r1[/COLOR][/B][/FONT]
[FONT=comic sans ms][B][COLOR=#b22222]              If WorksheetFunction.CountIf(r2, rngCell) = 0 Then[/COLOR][/B][/FONT]
[FONT=comic sans ms][B][COLOR=#b22222]                   Worksheets("SPR").Cells(j, 1) = Worksheets("MK_DB1").Cells(i, 2).Value[/COLOR][/B][/FONT]
[FONT=comic sans ms][B][COLOR=#b22222]              End If[/COLOR][/B][/FONT]
            If Worksheets("MK_DB1").Cells(i, 8).Value = "" Then
               Worksheets("SPR").Cells(j, 2) = Worksheets("MK_DB1").Cells(i, 9).Value
            Else
               Worksheets("SPR").Cells(j, 2) = Worksheets("MK_DB1").Cells(i, 8).Value 'AIC
            End If


           Worksheets("SPR").Cells(j, 3) = Worksheets("MK_DB1").Cells(i, 16).Value 'SW Ver
           Worksheets("SPR").Cells(j, 4) = Worksheets("MK_DB1").Cells(i, 24).Value 'Date Assigned
           If Worksheets("MK_DB1").Cells(i, 5) = 0 Then
              Worksheets("SPR").Cells(j, 8) = ""
           Else: Worksheets("SPR").Cells(j, 8) = Worksheets("MK_DB1").Cells(i, 5)
           End If 'SalesForce
           Worksheets("SPR").Cells(j, 5) = Worksheets("MK_DB1").Cells(i, 18).Value 'DateSPREntered


           If Worksheets("MK_DB1").Cells(i, 10) <> "" Then
            Sp = Split(Replace(Worksheets("MK_DB1").Cells(i, 10).Value, "(", ")"), ")")
            With Application
              X = .Index(Sp, .Match("10", Sp, 0) + 1)
            End With
            If Not IsError(X) Then Worksheets("SPR").Cells(j, 15).Value = X
           ElseIf Worksheets("MK_DB1").Cells(i, 11) <> "" Then
            Sp = Split(Replace(Worksheets("MK_DB1").Cells(i, 11).Value, "(", ")"), ")")
            With Application
              X = .Index(Sp, .Match("10", Sp, 0) + 1)
            End With
            If Not IsError(X) Then Worksheets("SPR").Cells(j, 15).Value = X
           End If
            'Batch # Left(Cells(I, 10), BatchP + 1)
             Worksheets("SPR").Cells(j, 21) = Worksheets("MK_DB1").Cells(i, 39).Value 'Date Occurred
             Worksheets("SPR").Cells(j, 25) = Worksheets("MK_DB1").Cells(i, 31).Value 'Symptom
             Worksheets("SPR").Cells(j, 30) = Worksheets("MK_DB1").Cells(i, 40).Value 'Submitted by Name
             Worksheets("SPR").Cells(j, 18) = Worksheets("MK_DB1").Cells(i, 48).Value 'Failed Component Serial Number
             Worksheets("SPR").Cells(j, 17) = Worksheets("MK_DB1").Cells(i, 49).Value 'Component Lot #
             Worksheets("SPR").Cells(j, 34) = Worksheets("MK_DB1").Cells(i, 23).Value 'Status
            j = j + 1
[FONT=comic sans ms][B][COLOR=#b22222]         Next rngCell[/COLOR][/B][/FONT]
         End If
        Next i

End Sub

Any ideas?

DThib

 

[Marcelo Branco]

This code is completely different from the first one.
I'm not fully understanding what you intend to do. Better create a new thread by showing the data before and after the macro and the logic used.

M.