Am I missing a variable?

[caparcona]

Public Sub comparator()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim note As Range

Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Set note = Worksheets("Sheet2").Range("D:D")

Stop

For Each ws2 In ws1.Rows
    If ws1.Cells(1, 1) = Space(0) Then
        Exit For
    Else
        For Each ws2 In ws2.Rows
            If ws2.Cells(1, 1) = Space(0) Then
                Exit For
        Else
            If ws2.Cells(1, 2) = ws1.Cells(1, 2) Then
                note.Copy (ws1.Cells(1, 4))
            End If
    End If
Next ws2

I'm brand new to VBA, and coding anything whatsoever. Up to this point everyone has been just doing the Ctrl+F thing for dozens of entries, and I've gotten annoyed at the status quo. I know a bit of code can automate this so I've taken it upon myself to try and learn VBA (maybe C# is next). The purpose of this is to compare two lists of employees and find a match. Upon finding a match, it will copy the item in sheet 2 column D to the same column in sheet 1. I know the final line is wrong but I can't figure out how to make it work. I think I've missed declaring a variable but I can't figure out what I need to declare.

 

[DacEasy]

Hi I tried your code but couldn't get it to work. So I wrote it with a for loop.

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("Sheet1").Select  'Select the Sheet1 and count rows
    With ActiveSheet
        sht1Lastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With

    Sheets("Sheet2").Select  'Select the Sheet2 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

 

[Fluff]

Duplicate to: How to compare two spreadsheets for identical employee numbers?

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.