CODEWITHSUNDEEP
excelvbavlookup
Martijn Dekkers
Martijn Dekkers

Reputation: 55

VBA Vlookup returns wrong results

I am currently trying to make a application where I can store sales in a single worksheet while using VBA.

When I try to use Vlookup to determine ProductID's prices so that I don't have to enter the value in myself the Vlookup returns always the same value "2015"

I have no clue where it's going wrong

This is the layout of the Sheet : Layout This is the layout of my Userform : Layout

And this is the code I use on my commandbutton :

Private Sub CommandButton1_Click()

Dim emptyRow As Long
Dim r As Range

Dim Productprijs As Integer
Dim productaantal As Integer
Dim Eindprijs As Integer



Sheet1.Activate

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 2).Value = TextBox2.Value
Cells(emptyRow, 3).Value = TextBox3.Value
Cells(emptyRow, 5).Value = TextBox4.Value

Productprijs = CInt(Application.VLookup(TextBox3.Value, "J2:L2000", 3, False))
productaantal = TextBox2.Value
Eindprijs = Productprijs * productaantal
Cells(emptyRow, 4).Value = Eindprijs


UserForm1.Hide

Could someone help me with my problem? it might just be a minor thing I am currently overlooking.

Thanks in regards, Martijn

Upvotes: 0

Views: 1607

Answers (2)

Andy
Andy

Reputation: 829

Your code is throwing a "2015 Error" because you are putting the TextBox3.Value inside the VLookup function as the first argument. Note that the following code works: 

Private Sub CommandButton1_Click()
    Dim emptyRow As Long
    Dim Price As Variant
    Dim Quantity As Double
    Dim Total As Double
    Dim x As Double

    'This finds the next empty row in the first table
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

    'Place the new values from the UserForm to the table
    Cells(emptyRow, 1).Value = TextBox1.Value 'Date
    Cells(emptyRow, 2).Value = TextBox2.Value 'Quantity
    Cells(emptyRow, 3).Value = TextBox3.Value 'ProductID
    Cells(emptyRow, 5).Value = TextBox4.Value 'Customer

    'Assign the value of the ProductID text box to x
    x = TextBox3.Value

    'Calculate the total price, using x instead of TextBox3.Value
    Price = Application.VLookup(x, Range("J2:L3"), 3, False)

    Quantity = TextBox2.Value
    Total = Price * Quantity
    Cells(emptyRow, 4).Value = Total

    UserForm1.Hide 
End Sub

This also eliminates the need to convert your Price variable using CInt. Hopefully someone else can articulate why the TextBox3.Value inside the VLookup throws an error?

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23505

There are 2 problems with your code; "J2:L2000" should be replaced with Range("J2:L2000") (the 2015 is the integer version of the 2015 error)

Your code will not work if the Vlookup cannot find Textbox3.Value: in that case it will return an error: the code should look more like this

Sub testv()
    Dim v As Variant
    Dim i As Long
    v = Application.VLookup(9, Range("A1:a3"), 1, False)
    If Not IsError(v) Then
        i = CLng(v)
    Else
        MsgBox "not Found"
    End If
End Sub

Upvotes: 3

Related Questions