Re: [ExcelVBA] VLookup error

 

From one Paul to another:

I know I'm a bit late to the show, and this probably isn't something you want to hear at this stage, but:

In my PERSONAL opinion, using the Application.Worksheetfunction methods is very "inefficient" when there's a straight VBA option available.

My reasoning is this:

All Excel functions are written in some compiled language (like C++, or VB, or some other "higher-level" language).

They inherently have the "overhead" of the Excel workbook to work around and through.

Whenever possible, I like to store the contents of the worksheet in VBA arrays, do all my VBA manipulations in VBA, then push the results back to Excel.

If you're working with a small worksheet (under 1000 rows), it doesn't make THAT much difference, but I routinely work with 50,000 to 100,000 rows.

If it were ME, I'd create a Dictionary object, and load the dictionary (using your concatenated value as the key field)

Dictionaries are VERY fast.

if you'd like to send me your file, I think I could quickly build an alternative for your vlookup function.

Paul

-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

On Wednesday, February 22, 2017 5:41 PM, "Paul Vermeulen paul.vermeulen@vulcantech.com.au [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:

 

Hi all, I have been stumped for the last two days. A simple VLookup function in VBA, with lists that should work, yet error after error - currently error 1004 - Unable to get the Vlookup property of the WorksheetFunction Class - which means the function cannot find a result. Please see if you can point me in the right direction.

*****************************

Data:

3 x 3 table, with the top left cell having a named range as a starting point. Data validation is List only, referring to the left-hand column of my Lookup table (which is a CONCATENATE of different text fields to give a unique value, e.g. =CONCATENATE(C3,", ",D3,", S/N ",E3) = 

"Neg Air, 4000, S/N 16630", formatted as General. Column 2 of the lookup table contains a Hyperlink to a PDF file location, with a text description for the hyperlink, e.g. G:\1. Company Name\2. Operations\Subcategory\5 - Subcategory Plant\Subcategory Plant Testing\2016\DOP TEST NPU 16630.pdf

A Vlookup in Excel works fine, and gives me the location of the PDF file, so I can print it using another routine.

****************************

Code:

Sub PrintEquipmentCertificates()

'Check the equipment boxes, if filled in, print the test certificate for that piece of equipment.

'Note that test certificates that are updated may not be reflected in here. The locations may have to be updated.

'Start value on range EquipmStart

    Dim i, j As Integer

    Dim FileLoc As String

    Dim EquipmStart As Variant

    Dim List_EquipCert As Variant

    Dim FilePath As String

    

    'On Error GoTo Err1:

    '3 x 3 table, loop through the whole table, if cell is not empty, look up the certificate and print.

    For i = 0 To 2

        For j = 0 To 2

    

            EquipmStart = Range("EquipmStart").Offset(i, j).Value

            List_EquipCert = Range("List_EquipCert").Name

            If EquipmStart <> "" Then

            

                FilePath = Application.WorksheetFunction.VLookup(EquipmStart, List_EquipCert, 2, False).Value

            

                Call PrintFile(FilePath)

                Call WaitTime

            

            Else

            

            End If

            

    

        Next j

    

    Next i

    

Err1:

End Sub

*************************

It should be noted that the variable "FilePath = Range......Value" gets the Hyperlink description address, not the Hyperlink address itself. Editing the description to show the full path does not work. Changing the Definition Type of variables from String/Range/Address to Variant/Hyperlink does not work.

 

Regards

Paul

__._,_.___

---

Posted by: Paul Schreiner <schreiner_paul@att.net>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___