compare cell content in range on 2 sheets

[mdavid]

There is no Debug.print output from the following code.
There are matching values in column B of the 2 sheets.

Sub Compare_species_id()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cell1, cell2 As Range
Set ws1 = ThisWorkbook.Sheets("Common Species")
Set ws2 = ThisWorkbook.Sheets("All Complaints")

For Each cell1 In ws1.Range("A2:A" & Range("A2").End(xlDown).Row)
  For Each cell2 In ws2.Range("A2:A" & Range("A2").End(xlDown).Row)
        If cell2.Offset(0, 1).Value = cell1.Offset(0, 1).Value Then
                  Debug.Print " complaint memp id = " & cell2.Offset(0, 1).Value
                  Debug.Print " complaint berc id = " & cell1.Value
        End If
    Next cell2
Next cell1
End Sub

Thanks for any help
David

 

[Hui]

David

Can you please supply a small sample of data so we can check your code.

 

[Hui]

at a guess you have made a small mistake:

Sub Compare_species_id()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cell1 as Range, cell2 As Range
Set ws1 = ThisWorkbook.Sheets("Common Species")
Set ws2 = ThisWorkbook.Sheets("All Complaints")
Dim lr1 as Long, lr2 as long
lr1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = ws2.Range("A" & Rows.Count).End(xlUp).Row

For Each cell1 In ws1.Range("A2:A" & lr1)
  For Each cell2 In ws2.Range("A2:A" & lr2)
        If cell2.Offset(0, 1).Value = cell1.Offset(0, 1).Value Then
                  Debug.Print " complaint memp id = " & cell2.Offset(0, 1).Value
                  Debug.Print " complaint berc id = " & cell1.Value
        End If
    Next cell2
Next cell1
End Sub

Also in your original code you had:

Dim cell1, cell2 As Range

What that actually means is

Dim Cell1 as Variant, Cell2 as Range

which isn't what you were after

 

[Hui]

This can be improved to:

Sub Compare_species_id()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim cell1 as Range, cell2 As Range
Set ws1 = ThisWorkbook.Sheets("Common Species")
Set ws2 = ThisWorkbook.Sheets("All Complaints")
Dim lr1 as Long, lr2 as long
lr1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = ws2.Range("A" & Rows.Count).End(xlUp).Row

For Each cell1 In ws1.Range("B2:B" & lr1)
  For Each cell2 In ws2.Range("B2:B" & lr2)
        If cell2.Value = cell1.Value Then
                  Debug.Print " complaint memp id = " & cell2.Value, " complaint berc id = " & cell1.Offset(0, -1)Value
        End If
    Next cell2
Next cell1
End Sub

 

[GraH - Guido]

Also in your original code you had:

Dim cell1, cell2 As Range

What that actually means is

Dim Cell1 as Variant, Cell2 as Range

Then many do make that mistake, for one I was unaware of this. Rather sure I have that mistake in my notes somewhere as a supposed best practice. Hell no! Darned Excel trainers...

 

[mdavid]

Thanks for all your help, much appreciated.