Hi Derek
Yes, David S picked up on this as well. I was playing around with type definitions in trying to faultfind, and Variant is never a good definition to have. The code now works, and my new boss will be a bit happier tomorrow when I finish this.
Thank you both for your assistance.
Regards
Paul
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Thursday, 23 February 2017 6:56 PM
To: ExcelVBA@yahoogroups.com
Subject: 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: 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 (5) |
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
----------------------------------
Tidak ada komentar:
Posting Komentar