VLookUp not returning expected results

reddale · Jun 18th 2016

Hello,

I have this code. It does exactly what is required even if the range("a2:d15") is not sorted.

Code

On Error GoTo MyErrorHandler:


Dim disname As String
    disname = Sheet11.Range("b47")
        If Len(disname) > 0 Then
            licence = Application.WorksheetFunction.VLookup(disname, Sheet5.Range("a2:d15"), 2, False)
                Sheet11.Range("b46").Value = licence
                  
                    
            adress = Application.WorksheetFunction.VLookup(disname, Sheet5.Range("a2:d15"), 3, False)
                Sheet11.Range("b49").Value = adress
  
            
            dealer = Application.WorksheetFunction.VLookup(disname, Sheet5.Range("a2:d15"), 4, False)
                Sheet11.Range("b44").Value = dealer


  
  
Else
  
End If
Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
  Exit Sub
End If

Display More

Then I have this code that doesn't work properly. It works to a certain point, but where the VLookUp should be returning a result it's doing nothing. The Application.WorksheetFunction.VLookUp looks to my eye to be the same in both lots of code.

Code

Dim serial_text As Range
        Set serial_text = Sheet17.Range("b53")
            serial_text.NumberFormat = "General"


Dim clear_sheet As Range
        Set clear_sheet = Sheet17.Range("b40:b61")
            clear_sheet.ClearContents
   
Dim stk_valid As Range
        Set stk_valid = Sheet17.Range("B40")
            stk_valid.Validation.Delete
            
Dim acq_valid As Range
        Set acq_valid = Sheet17.Range("b41")
            stk_valid.Validation.Delete
    
    
On Error GoTo MyErrorHandler:


Dim acqstk As String
    acqstk = Sheet17.Range("b40")
        
            AcqNum = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 14, False)
                Sheet17.Range("b41").Value = AcqNum
                
            acqscr = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 27, False)
                Sheet17.Range("b42").Value = acqscr
                  
            acqdate = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 3, False)
                Sheet17.Range("b43").Value = acqdate
                
            acqlicence = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 13, False)
                Sheet17.Range("b46").Value = acqlicence
                
            acqname = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 10, False)
                Sheet17.Range("b47").Value = acqname
                
            acqadress = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 11, False)
                Sheet17.Range("b49").Value = acqadress
            
            acqrego = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 4, False)
                Sheet17.Range("b50").Value = acqrego
                
            acqmake = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 6, False)
                Sheet17.Range("b51").Value = acqmake
              
            acqmodel = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 7, False)
                Sheet17.Range("b52").Value = acqmodel
            
            acqserial = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 5, False)
                Sheet17.Range("b53").Value = acqserial
                
            acqcalibre = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 8, False)
                Sheet17.Range("c54").Value = acqcalibre
                
            acqftype = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 22, False)
                Sheet17.Range("b55").Value = acqftype
            
            acqloadact = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 23, False)
                Sheet17.Range("b56").Value = acqloadact
                
            acqatype = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 24, False)
                Sheet17.Range("b57").Value = acqatype
                
            acqptype = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 25, False)
                Sheet17.Range("b58").Value = acqptype
            
            acqmagcap = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 26, False)
                Sheet17.Range("b59").Value = acqmagcap
                
            acqpisbbllen = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 9, False)
                Sheet17.Range("b60").Value = acqpisbbllen
                
            acqprimbblserial = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 28, False)
                Sheet17.Range("b61").Value = acqprimbblserial
            
            acqefims = Application.WorksheetFunction.VLookup(acqstk, Sheet3.Range("a2:ad4000"), 30, False)
                Sheet17.Range("b62").Value = acqefims
            




UPDATPAB31.Show


Dim serial_gen As Range
    serial_gen = "b53"
        serial_gen.NumberFormat = "@"


Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
  Exit Sub
End If

Display More

Any suggestions as to why the second lot of code doesn't play?

Regards

skywriter · Jun 18th 2016

Re: VLookUp not returning expected results

Quote

It works to a certain point, but where the VLookUp should be returning a result it's doing nothing.

But you don't tell us where that is, so?

reddale · Jun 19th 2016

Re: VLookUp not returning expected results

Quote from reddale;772719

but where the VLookUp should be returning a result it's doing nothing

Quote from skywriter;772743

But you don't tell us where that is, so?

In the second group of code, VLookUp isn't returning a result, it isn't doing anything

skywriter · Jun 19th 2016

Re: VLookUp not returning expected results

Why did you edit my quote?
You said it works to a certain point, so that is not the case?

skywriter · Jun 19th 2016

Re: VLookUp not returning expected results

I could probably figure this out in just a few minutes with a workbook, but my hunch is your problem lies with what you are trying to look up.

First why does what is in this cell have to be declared as a string?

Code

Dim acqstk As String
acqstk = Sheet17.Range("b40")

Instead of putting that variable in the lookup you could just simply put Sheet17.Range("b40").Value instead.

The item you are looking up must be in the first column in the lookup data.

I suggest you make a formula in a blank cell.
=B40=?
You know what's in B40 so look in the first column of the lookup data and find the cell the match is in and put that cell's address into where the question mark is.

If the result of the formula is FALSE then there's an issue and you are not getting a match since Excel doesn't see them as equal.

One example of where this happens is when numbers are imported and they have decimals that don't show up in the cells but exist in the underlying number.

One cell actually has 1.0005 and the lookup cell has 1.00. The user sees 1.00 in both cells because of the way they are formatted, but Excel doesn't see these as equal and they don't match.

I don't know what your data is, but my hunch is the issue is the values are not equal.

You might also want to comment out your on error statement to see if you get any errors and what they are and what line they are on.

If none of that helps I suggest uploading a workbook.

reddale · Jun 19th 2016

Re: VLookUp not returning expected results

Thank you for the detailed reply skywriter

I will work through your suggestions and report back.

reddale · Jun 19th 2016

Re: VLookUp not returning expected results

Bear with me, there are a few answers

Quote from skywriter;772749

I could probably figure this out in just a few minutes with a workbook, but my hunch is your problem lies with what you are trying to look up.

I started to make a sample workbook to post, but it ends up being too large and the names, addresses etc of our clients are too numerous to replace.

In the instance of the first piece of code I posted, it is text formatted as General

In the instance of the second piece of code I posted, it is a number formatted as General

Quote
First why does what is in this cell have to be declared as a string?
Code
Dim acqstk As String
acqstk = Sheet17.Range("b40")
Instead of putting that variable in the lookup you could just simply put Sheet17.Range("b40").Value instead.

Changing this worked...but

This is where I get confused because of lack experience I guess

When the first sample of code I posted runs (via a Worksheet_Change event) it works. When I say it works, the user enters a name in a cell and the other cells poplulate with data from another sheet. It works whether the range is sorted or not.

When the second code sample runs (fired by clicking a command button) and the user enters a number in a cell, nothing happens. Leave that number in the cell and run the code again and it works.

Quote

You might also want to comment out your on error to see if you get any errors and what they are and what line they are on.

I had gone down that roadalready, the error appears in the first line of the VLookUp code until a number is entered in B40 and the code is ran again, then there is no error

reddale · Jun 19th 2016

Re: VLookUp not returning expected results

While I was trying to make my above answer make sense (still don't know if it does)

I came up with a solution that invovles Data Validation.

Once I did that, my code does what I wanted to do. I also found a couple of errors in related code, so a couple more things work better now as well.

With my issues sorted, the new code (2nd lot in the original post) runs in half the time that the old code I was using that placed VLookUp formulas into the cells did. I don't know if half a second is significant when talking about VBA, but thats' the result I got

skywriter, your suggestion about the string variable got the ball rolling.

Thank you

skywriter · Jun 20th 2016

Re: VLookUp not returning expected results

My pleasure, thanks for the feedback. :cool:

**rory** · Jun 21st 2016

Re: VLookUp not returning expected results

Just as an additional point, it's quite inefficient to lookup the same lookup value repeatedly, and also to create a range variable just to perform one operation. You can reduce your code quite a lot - for example:

Code

Dim rgData                As Range
    Dim vMatch


    Set rgData = Sheet3.Range("a2:ad4000")


    With Sheet17
        .Range("b53").NumberFormat = "General"
        .Range("b40:b61").ClearContents
        .Range("B40").Validation.Delete
        .Range("b41").Validation.Delete


        vMatch = Application.Match(Sheet17.Range("b40"), Sheet3.Range("a2:a4000"), 0)


        If Not IsError(vMatch) Then


            .Range("b41").Value = rgData.Cells(vMatch, 14).Value
            .Range("b42").Value = rgData.Cells(vMatch, 27).Value
            .Range("b43").Value = rgData.Cells(vMatch, 3).Value
            .Range("b46").Value = rgData.Cells(vMatch, 13).Value
            .Range("b47").Value = rgData.Cells(vMatch, 10).Value
            .Range("b49").Value = rgData.Cells(vMatch, 11).Value
            .Range("b50").Value = rgData.Cells(vMatch, 4).Value
            .Range("b51").Value = rgData.Cells(vMatch, 6).Value
            .Range("b52").Value = rgData.Cells(vMatch, 7).Value
            .Range("b53").Value = rgData.Cells(vMatch, 5).Value
            .Range("c54").Value = rgData.Cells(vMatch, 8).Value
            .Range("b55").Value = rgData.Cells(vMatch, 22).Value
            .Range("b56").Value = rgData.Cells(vMatch, 23).Value
            .Range("b57").Value = rgData.Cells(vMatch, 24).Value
            .Range("b58").Value = rgData.Cells(vMatch, 25).Value
            .Range("b59").Value = rgData.Cells(vMatch, 26).Value
            .Range("b60").Value = rgData.Cells(vMatch, 9).Value
            .Range("b61").Value = rgData.Cells(vMatch, 28).Value
            .Range("b62").Value = rgData.Cells(vMatch, 30).Value


        End If
    End With


    UPDATPAB31.Show


    Range("b53").NumberFormat = "@"

Display More

reddale · Jun 21st 2016

Re: VLookUp not returning expected results

Thank you Rory. I don't think I'll ever get to the level of you guys. Thats why I come here. Thank you for the code. It sure is shorter than mine. I'll have to learn a bit more a bout your code whe I get a chance.

Regards

**rory** · Jun 21st 2016

Re: VLookUp not returning expected results

Quote from reddale;772856

I don't think I'll ever get to the level of you guys.

There's no reason why not - it just takes time. We all started from scratch at some point.

reddale · Jun 21st 2016

Re: VLookUp not returning expected results

Very true. I have learnt a heap in the last few weeks.

VLookUp not returning expected results

Participate now!

Share