Am I missing a variable?

Status
Not open for further replies.

caparcona

New Member
Joined
Mar 9, 2022
Messages
19
Office Version
  1. 2021
Platform
  1. Windows
VBA Code:
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.
 
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi I tried your code but couldn't get it to work. So I wrote it with a for loop.

VBA Code:
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
 

Attachments

  • forCompareNames.jpg
    forCompareNames.jpg
    51.3 KB · Views: 5
Last edited:
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top