CODEWITHSUNDEEP
excelvba
Rob
Rob

Reputation: 428

My VBA Vlookup is returning N/A. What am I doing wrong?

So I have a list that was pre-assembled and I'm attempting to now add more to the list from another workbook I have. I figured I could use VBA to create a macro to perform a VLookup to retrieve and populate the added fields.

My VBA:

Option Explicit
Sub CompareUntimed()
    Dim wb As Workbook
    Dim utsheet As Worksheet
    Dim utlastrow, f9lastrow, J As Long
    Dim f9sheet As Worksheet
    Dim ctr As Integer
    Set wb = Workbooks.Open(get_user_specified_filepath())
    Set utsheet = wb.Sheets(2)
    utlastrow = utsheet.Cells(Rows.Count, "A").End(xlUp).Row
    Set f9sheet = ThisWorkbook.Sheets("Part List")
    f9lastrow = f9sheet.Cells(Rows.Count, "A").End(xlUp).Row
    For J = 2 To f9lastrow
        f9sheet.Range("G" & J) = Application.VLookup(f9sheet.Range("H" & f9lastrow), utsheet.Range("S2:S" & utlastrow), 17, False)
        f9sheet.Range("F" & J) = Application.VLookup(f9sheet.Range("H" & f9lastrow), utsheet.Range("S2:S" & utlastow), 10, False)
    Next J
End Sub

This is the Workbook where I'm getting #N/A instead of the proper values enter image description here

This is the Workbook I'm attempting to match to and take values from enter image description here

I'm attempting to use the UniqueID columns I've created which is the last column in each workbook and I'm attempting to add the dates and modified by columns to my new workbook.

Upvotes: 0

Views: 69

Answers (1)

Tim Williams
Tim Williams

Reputation: 166391

Sub CompareUntimed()
    Dim wb As Workbook
    Dim utsheet As Worksheet
    Dim utlastrow, f9lastrow, J As Long
    Dim f9sheet As Worksheet
    Dim ctr As Long

    Set wb = Workbooks.Open(get_user_specified_filepath())
    Set utsheet = wb.Sheets(2)

    Set f9sheet = ThisWorkbook.Sheets("Part List")
    f9lastrow = f9sheet.Cells(Rows.Count, "A").End(xlUp).Row

    For J = 2 To f9lastrow
        m = Application.Match(f9sheet.Range("H" & J), utsheet.Columns("S"), 0)
        If Not IsError(m) Then
            f9sheet.Range("G" & J).Value = utsheet.Cells(m, "K").Value
            f9sheet.Range("F" & J).Value = utsheet.Cells(m, "R").Value
        Else
            f9sheet.Range("F" & J).Resize(1,2).Value = "???"
        End If
    Next J
End Sub

Upvotes: 1

Related Questions