VBA Vlookup loop issue

Question

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

Scott Craner · Accepted Answer · 2017-07-11T19:25:05.243

1

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.

edited Jul 11 '17 at 19:25

answered Jul 11 '17 at 13:23

Scott Craner

148,073
10
49
81

Thanks! Looks like the 3rd criterion slipped my mind for a moment. Additionally, realized that wsThat does not exist in my code - was used for switching sheets but i just moved all the data to one sheet to resolve that issue and it is now wsThis. Unfortunately, this error persists in the line For Each aCell In .Range("V2:V") and I'm unsure why. Any additional edits would help! Thanks again. – Shin Jul 11 '17 at 19:21
@Shin see edit. you can change the `65000` to what ever your max range is. – Scott Craner Jul 11 '17 at 19:25
Hi, yes I understand that issue, I was hoping the V2:V would take me throughout the entire column until no value was found in the next row as the max range is variable. Additionally, For some reason the vlookup is returning 'not found' for about 10 instances and I'm unsure why because they lie within the range A2:D3500. – Shin Jul 11 '17 at 19:57
That will not work, you will need to find the end of the data yourself. See [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) on how to find the last row. As to the `Not Found` check those values and make sure there are no spaces or any other non visible characters that would cause it not to find a match. – Scott Craner Jul 11 '17 at 20:00
Scott, thank you for the words of advice, I believe that is what is what I was looking for. One last note, I noticed that the cells that are not returning the appropriate data have an error notice on the top left hand corner of the cell saying 'the number in this cell is formatted as text or preceded by an apostraphe.' I've tried to convert it to a number, and backspace before the numerical code but to no avail. Do you have any advice in this regards? Thanks again. – Shin Jul 11 '17 at 20:20

score 0 · Answer 2 · answered Jul 11 '17 at 13:28

0

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.

answered Jul 11 '17 at 13:28

Plagon

2,689
1
11
23

VBA Vlookup loop issue

2 Answers2