Excel vba to compare values

Question

I have 2 excel sheets sheet A and Sheet B. In Sheet A I have a named range containing Names of employees .Column B is blank. In sheet 2 I have a named range for a list of employees and days of attendance.The employee names in both sheets are not in the same order. I need to compare the name in sheet A with sheet B and when there is a match I need to copy the attendance days and put it in the Sheet A in column B against the name. I am looking for help in VBA

Any help is appreciated

this is what I have so far

    Sub ADDCLM()
    On Error Resume Next
    Dim Dept_Row As Long
    Dim Dept_Clm As Long
`   Dim table1
    Dim table2
    Dim cl
    table1 = Sheet1.Range("A2:A13")
    table2 = Sheet2.Range("A2:A13")
    Dept_Row = Sheet1.Range("B2").Row
    Dept_Clm = Sheet1.Range("B2").Column
    For Each cl In table1
      Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, table2, 2, False)
      Dept_Row = Dept_Row + 1
    Next cl
    MsgBox "Done"
    End Sub

What code have you tried so far? You will get much better help if you post your current efforts and some description of what isn't working. — enderland
Just use VLOOKUP function. It is designed for this. No need to use VBA unless it's a requirement, in which case post the code you have already attempted. — David Zemens
=IF(IsNA(Vlookup(A1,Sheet2!A:B,2,False)),"not found",VLOOKUP(A1,Sheet2!A:B,2,False)). — David Zemens

David Zemens David Zemens · Accepted Answer · 2013-08-24T15:01:08

Try this, untested. Use range objects instead of variants. Your row/column counter variables are unnecessary/redundant and I remove them. You need table2 to be defined as a two column range to use the VLOOKUP function and return the value from column B. Also, get rid of On Error Resume Next that's a bad practice to start, better to use error handling or test for error conditions and re-route.

Sub ADDCLM()

Dim table1 As Range
Dim table2 As Range
Dim cl As Range
Set table1 = Sheet1.Range("A2:A13")
Set table2 = Sheet2.Range("A2:B13")  '# Modified this to 2 columns
For Each cl In table1
    If Not IsError(Application.Match(cl,table2.columns(1),False) Then
        cl.Offset(0,1) = Application.WorksheetFunction.VLookup(cl, table2, 2, False)
    End If
Next 
MsgBox "Done"
End Sub

Excel vba to compare values

1 Answers