CODEWITHSUNDEEP
excelvbavlookup
SVB
SVB

Reputation: 3

VBA Vlookup not finding values that exist

I am working with two different Worksheets in one workbook. My task is to look up the model# of a product from Sheet1, find that same model# in Sheet2, and get the cost of that product, which is located a few columns away.

So naturally, I tried to use Vlookup, because that function is enough for this query.

I will post my code below, and then explain the problems I am facing. I am new to VBA and have searched many many different Stack posts, and tried the various solutions, to no avail.

Private Sub CommandButton1_Click()

Dim tbdCell As Range
Dim model As Range
Dim cell As Range
Dim PAsheet As Worksheet
Dim DB As Worksheet
Dim target As Variant
Set DB = Worksheets("Database")
Set PAsheet = Sheets("Pricing Agreement")


Set tbdCell = Range("N2:N4700")
On Error GoTo ErrHandler:
For Each cell In tbdCell
    Set model = cell.Offset(0, -6)
    cell = WorksheetFunction.VLookup((CStr(model)), PAsheet.Range(CStr("C2:D2000")), 6, True)
    Next cell

    Exit Sub


ErrHandler:
    Select Case Err.Number
        Case 0

        Case 1004
            cell = "missing"
            Resume Next
        Case Else
            MsgBox Err.Number & vbNewLine & Err.Description
            Exit Sub

    End Select

End Sub

So upon debugging and testing, most things work until we get to the line where I use the Vlookup function. I invariably get error 1004, even though the data exists in the other spreadsheet. So the cells that I need to fill will always fill with "missing" as posted above in the Error Handling Code.

I tried using the Application version of the function. I tried using different variables and declaring them as Variant type. I even tried making the table_array range just one row with 2 column coverage, in an attempt to force a match for one particular model #. So far, to avoid a type mismatch, I cast 'model'(the model#) into a String, and I also cast the search range in PAsheet to String. The final thing I tried was to not search for an exact match(last argument was set to true)

So in anticipation of future questions about the data that the Vlookup is based on, I will include necessary information about how both sheets are formatted.

Info that you may need:

We start in column N, where the prices are missing in Sheet1(Database).

I set model to the value in the same row, 6 columns to the left(Column H).

Testing with MsgBox proved this to work for me, and on debug, the model variable displays the correct info, so this isn't the issue.

In PAsheet, the model #s are in column C. Originally I made the search table from C2:C2000 or so, but I was led to believe that you need at least a two column table for Vlookup to work, so I changed C2000 to D2000. Now the search range is a two column table.

In PAsheet, the cost of the product is in Column H, which is 5 away from column C. I need this value, so I put 6 in the column_index argument. It was 5 before, because I thought that you didn't count the first column, but I fixed that.

Finally I mostly tested with "False" as the last argument, but either way it doesn't work.

So after trying more than two dozen variations and strategies, I still get "missing" in the cells that I need to fill.

So, what am I doing wrong here? Thanks in advance.

Upvotes: 0

Views: 1700

Answers (1)

urdearboy
urdearboy

Reputation: 14580

If you are trying to return the 6th value from Column C your range needs to be updated to `PAsheet.Range("C2:H2000")

cell.Value = WorksheetFunction.VLookup(cell.Offset(, -6), PAsheet.Range("C2:H2000"), 6, False)

Upvotes: 2

Related Questions