How to compare two spreadsheets for identical employee numbers?

[caparcona]

I just promoted into this position and am learning Excel for the first time. I have a "not working" sheet and a "didn't work yesterday" sheet in the same workbook. I'm trying to compare people who skipped work yesterday against who skipped work today and "Ctrl+C, CRTL+V" was getting old very fast so I decided to try to teach myself VBA in order to get more work done. Thus far I have:

Sub comparison()
Dim B1 As Range
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")
Dim ws1 As Worksheet
Set ws1 = Application.Worksheets("Current List")
Dim ws2 As Worksheet
Set ws2 = Application.Worksheets("Prev NCNS Here")


With Sheets(ws1)
For Each B1 In ws2.Range("B:B",
End With

With Sheets(ws2)
End With

End Sub

I've gotten a little stuck since I am not sure why I Dimmed B1 As Range last week and I'm not sure how to do anything further. I need to take Column B "Current List" (Sheet 1) and compare it against Column B "Prev NCNS Here" (Sheet 2), and eventually I want to paste whatever is in Column C Prev NCNS Here into Column C Current List (I have already done this before, I don't need help with Column C).

What I need help with is how to compare the B columns. Copying the Column C info for that row will be the confirmation that the person is on both lists.

 

[gordsky]

I havent been able to test this but maybe something like this will work for you

Sub Comparison()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Rng1 As Range, Rng2 As Range, Ans As Range, cel As Range
Dim LkFor As String

Set ws1 = Sheets("Current List")
Set ws2 = Sheets("Prev NCNS Here")
Set Rng1 = ws1.Range("B" & ws1.Cells(Rows.count, 2).End(xlUp).row)
Set Rng2 = ws2.Range("B" & ws2.Cells(Rows.count, 2).End(xlUp).row)

For Each cel In Rng1
LkFor = cel
                       
Set Ans = Rng2.Find(what:=LkFor, LookIn:=xlValues, _
                lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
               
    If Not Ans Is Nothing Then
        ws2.Cells(Ans.row, 3).Copy ws1.Cells(cel.row, 3)
    Else
        MsgBox "Employee Number " & cel & " not found"
    End If

Next cel

End Sub

 

[DacEasy]

Here is my answer to the other thread that was closed. Same code just update the sheet names and change column D to C.

Sub CompareNames()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim sht1Lastrow As Long
    Dim sht2Lastrow As Long
    Dim i As Long
    Dim j As Long
    Dim note As Long
 
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")

    Sheets(ws1).Select  'Select ws1 and count rows
    With ActiveSheet
        sht1Lastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With

    Sheets(ws2).Select  'Select ws2 and count rows
    With ActiveSheet
        sht2Lastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With
 
    note = 1  'Begin with row 2 due to Headers
 
    For i = 1 To sht1Lastrow
        For j = 1 To sht2Lastrow
            If ws2.Cells(j, 1) = ws1.Cells(i, 1) Then   'Check for duplicate names
                note = note + 1
                ws2.Cells(note, 4).Value = ws2.Cells(j, 1).Value
                ws1.Cells(note, 4).Value = ws2.Cells(j, 1).Value
            End If
        Next j
    Next i
 
End Sub

 

[Peter_SSs]

@DacEasy
When posting vba code, please use the various available code tags. My signature block below has more details.
I have added the tags this time in the post above.

 

[DacEasy]

@DacEasy
When posting vba code, please use the various available code tags. My signature block below has more details.
I have added the tags this time in the post above.

Sorry, thanks for pointing that out.

 

[caparcona]

Thanks for the help, guys! Sorry I haven't responded, work has been swamped. You've been a big help, though, and I've learned from your answers to make my own code. This site is the best!