Kamis, 23 Februari 2017

Re: [ExcelVBA] VLookup error

 

Dear Paul

Maybe I am wrong because I cannot test this but the line 

            List_EquipCert = Range("List_EquipCert").Name

returns a variant but the VLookup  needs a range for its second parameter ?

Regards

Derek Turner +++


From: "'David Smart' smartware.consulting@gmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Thursday, 23 February 2017, 0:08
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 -----
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: Derek Turner <g4swy@yahoo.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (4)
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

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

.

__,_._,___
Diposting oleh EXCEL FEED LEARNING di 00.55

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)