Why am I getting Run time error 1004 using Vlookup?

Question

I have a macro where I go through each row of Newest_closed worksheet and: - get the NCASTS string in column A - look for the string in Newest_opened worksheet using Vlookup - if found, get contents of Column K and call it "Resp_Unit2" - paste value of "Resp_Unit2" in Column K of Newest_closed worksheet NOTE: Newest_opened and Newest_closed are Public-dimensioned strings, and actual names of the worksheets are created in another Sub.

When I reach the line of code that uses Vlookup, I get "Run-time error 1004: Application-defined or object-defined error". Why am I getting this error?

I have used "Immediate" window to query values of Last_row_1, Last_column_1, and NCASTS (for loop counter x = 2). All queries return valid values.

I have also used "Immediate" window to execute:

Worksheets(Newest_opened).Range(Cells(1, 1), Cells(Last_row_1, Last_column_1)).Select

Excel shows the correct range of cells selected in the worksheet.

    Sub Assign_Resp_Unit_2()
    'This sub does the following:
    '
    '   - for each NCASTS in the Newest_closed worksheet:
    '       - searches in the Newest_opened worksheet for that NCASTS
    '       - if NCASTS found, gets the value from the "Responsible Unit"    column and pastes it in the same column in the Newest_closed worksheet
    '
    Dim x As Integer
    Dim NCASTS As String
    Dim Resp_Unit2 As String
    Dim Last_row_1 As Long
    Dim Last_column_1 As Long
    Dim New_opened_rng As Range
   '
   'Find range of Newest_opened worksheet
   Worksheets(Newest_opened).Activate
   Last_row_1 = Cells(Rows.Count, 1).End  (xlUp).Row                           'get number of last row in table
   Last_column_1 = Cells(1, Columns.Count).End(xlToLeft).Column              'get number of last column in table
   '
   'Set the variable "Newest_opened_range" to be the Range of the Newest_opened worksheet
   Set New_opened_rng = Worksheets(Newest_opened).Range(Cells(1, 1), Cells(Last_row_1, Last_column_1))
   '
   'search Newest_opened worksheet for Responsible Unit
   '
   'Loop through each cell in Column A of Newest_closed until a blank cell is encountered (i.e. end of entries)
   '
   Worksheets(Newest_closed).Activate
   x = 2   'initialize row counter
   Do Until IsEmpty(Cells(x, 1))
       '
       'Get NCASTS number from Column A of Newest_closed worksheet
       NCASTS = Cells(x, 1)
       '
       'Search for NCASTS number in Newest_opened worksheet and find  corresponding Responsible Unit entry;
       'Preset string variable Resp_Unit2 to "Not found" for case where NCASTS number is not in the range
       '
       Resp_Unit2 = "Not found"
       On Error Resume Next
       Resp_Unit2 = Application.WorksheetFunction.VLookup(NCASTS, New_opened_rng, 11, False)
       '
       'Paste value of Resp_Unit2 to Responsible Unit column (column L) for same NCASTS in Newest_closed worksheet
       '
       Sheets(Newest_closed).Cells(x, 12).Value = Resp_Unit2
       '
       'Move to next row of Newest_closed worksheet
       '
       x = x + 1
   '
   Loop
   '
   End Sub

An minimal reproducible example would help. Comment out On Error Resume Next for starters to see what's actually going on. — SJR
Welcome to SO. First of all, delete On Error Resume next, because it hides errors, but they still occur. Second, VLOOKUP can return an error in diferent situation. If nothing is found, it will return an error, so make sure what you are searching is on it, and it's exactly the same (no hidden chars, extra blanks). Secoond, make sure range New_opened_rng got 11 columns — Foxfire And Burns And Burns
Possible duplicate of How to error handle 1004 Error with WorksheetFunction.VLookup? — GSerg

Cyril Cyril · Accepted Answer · 2019-07-31T13:54:08

Rather than vlookup, since you're working in a specific column out in yonder (and hoenstly ensuring it's the 11th column of the range is its own PITA (subjective, I know)), you might try index/match to ensure you're only having to specify your lookup and output ranges. Will make a couple changes, like ensuring to qualify ALL ranges (even the cells inside of range):

Dim OutputRng as Range, SearchRng as Range, NCASTS as String, Resp_Unit2 as String, Last_row_1 as Long
With Worksheets(Newest_opened)
    Set SearchRng = .Range(.Cells(1,1),.Cells(Last_row_1,1)) 'Assumes Column A is searched
    Set OutputRng = .Range(.Cells(1,11),.Cells(Last_row_1,11)) 'Assumes Column K has otuput
    Last_row_1 = .Cells(.Rows.Count, 1).End(xlUp).Row  'qualified
    Do Until IsEmpty(.Cells(x, 1)) 'qualified this, but may want to try a for or for each loop?
        NCASTS = .Cells(x, 1).Value 'qualify it, don't use .activate and added .value
        Resp_Unit2 = Application.Index(OutputRng, Application.Match(NCASTS, SearchRng, 0))
        x = x+1
    Loop
End With

Related to your error, you might not have an exact match, which would throw an error; using WorkSheetFunction you will get an error that flags, similar to the worksheet's #VALUE. FoxFire touches on that in the comments section of the post which is quite important.

Why am I getting Run time error 1004 using Vlookup?

1 Answers