CODEWITHSUNDEEP
vbaexcelvlookup
Shin
Shin

Reputation: 33

VBA Vlookup loop issue

Im working with a large set of data in which my workbook contains multiple sheets of data and I'd like to Vlookup relevant data using a column in the sheet "URR" that is obtained from the sheet "SAP Pull". So since SAP Pull is the master sheet with all the data and URR contains the column with relevant data, im copying over that column of relevant data to SAP Pull and attempting to write a vlookup loop to find the corresponding data for each row of the column. The problem is I keep getting thrown an error called Method 'range' of object'_Worksheet failed. Here is my code as I'm confused to why I keep getting this issue.

Dim wsThis As Worksheet
Dim aCell As Range

Sheets("URR").Columns(1).copy Destination:=Sheets("SAP Pull").Columns(22)
Set wsThis = Sheets("SAP Pull")
    With wsThis
        For Each aCell In .Range("V2:V")
            .Cells(aCell.Row, 23) = Application.WorksheetFunction.VLookup( _
                                aCell.Value, wsThat.Range("A2:D3500"), False)
            Next aCell
         End With
End Sub

Upvotes: 0

Views: 1131

Answers (2)

Scott Craner
Scott Craner

Reputation: 152605

You were missing the 3rd criterion which tells Excel which column you want returned.

You also need to deal with the problem whenaCell.Value is not found in wsThat.Range("A2:D3500"), it will stop your code also when the value is not found.

You need to trap the error in some way.

Dim wsThis As Worksheet
Dim aCell As Range

Sheets("URR").Columns(1).copy Destination:=Sheets("SAP Pull").Columns(22)
Set wsThis = Sheets("SAP Pull")
    With wsThis
        For Each aCell In .Range("V2:V65000")
            .Cells(aCell.Row, 23) = "Not Found"
            On Error Resume Next
            .Cells(aCell.Row, 23) = Application.WorksheetFunction.VLookup( _
                                aCell.Value, wsThat.Range("A2:D3500"),4, False)
            On Error Goto 0
         Next aCell
     End With
End Sub

This will put Not Found in each cell, and if the value is found in the range it will put the value from column D in the field.

Upvotes: 1

Plagon
Plagon

Reputation: 3138

You are missing a Statement in the VlookUp. 

.Cells(aCell.Row, 23) = Application.WorksheetFunction.VLookup( _
                                aCell.Value, wsThat.Range("A2:D3500"),2, False)

The 3rd Statement is for the Column that will be returned.

Upvotes: 0

Related Questions