Re: [ExcelVBA] VLookup error

 

Hi Dave, unable to clean it up sufficiently to anonimise it, and still maintain functionality. Happy to privately send it to you and possibly others that may ask.

If not suitable, I will send to a moderator for upload.

Paul

---

From: ExcelVBA@yahoogroups.com <ExcelVBA@yahoogroups.com> on behalf of 'David Smart' smartware.consulting@gmail.com [ExcelVBA] <ExcelVBA@yahoogroups.com>
Sent: 23 February 2017 10:08
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] VLookup error

 

 

Can you upload your file to the files area of the group?  I think this one is a bit complex to speculate from just the code.

Regards, Dave S
 

----- Original Message -----

From: Paul Vermeulen paul.vermeulen@vulcantech.com.au [ExcelVBA]

To: ExcelVBA@yahoogroups.com

Sent: Thursday, February 23, 2017 9:27 AM

Subject: [ExcelVBA] VLookup error

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 Vermeulen <paul.vermeulen@vulcantech.com.au>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

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

.

__,_._,___