From David S
Hi Paul
I simplified the offending statement ...
Dim xx As Variant
xx = Application.WorksheetFunction.VLookup(EquipmStart, List_EquipCert, 2, False)
(i.e. took off the hyperlink item)
I get the 1004 error.
Now, it's going to be because of List_EquipCert. That is a String value of "=LookupLists!$F$3:$G$25". So your VLookUp effectively is
xx = Application.WorksheetFunction.VLookup(EquipmStart, "=LookupLists!$F$3:$G$25", 2, False)
i.e. it's trying to search inside that string, rather than using it as a lookup range. But it can't, so it fails.
Unfortunately, you have two variant variables defined List_EquipCert and EquipmStart. Unless what you're expecting to see really is variant in nature (i,e, one thing sometimes and something else other times) then it's not good to use Variant, as it hides problems.
List_EquipCert = Range("List_EquipCert").Name
returns the name of the range to the variable - i.e. it is a string. But VLookUp needs a Range in that parameter position. So, changing the Dim to
Dim List_EquipCert As Range
would immediately tell you that you have a problem there,
Now there are two approaches to getting rid of this problem:
1) make List_EquipCert a Range variable and put a range reference in it, or
2) change the VLookUp parameters so that the second parameter is converted to a Range from a String.
(2) is illogical, as you'd be converting from range to string and back to range, so use (1),
Dim List_EquipCert As Range
Set List_EquipCert = Range("List_EquipCert")
(Note that you'd do the Set outside the loops, as it only needs to be done once.)
xx = Application.WorksheetFunction.VLookup(EquipmStart, List_EquipCert, 2, False)
and xx now contains "G:\1. Breakthrough\2. Operations\ASBESTOS WORKS\5 - Asbestos Plant\Asbestos Plant Testing\2016\DOP TEST NPU 16630.pdf"
so we're halfway there.
========================
FilePath = Application.WorksheetFunction.VLookup(EquipmStart, List_EquipCert, 2, False).Hyperlink
will now fail with a 424 error because the return from the VLookUp is a string, not an object, so can't have a hyperlink attribute.
I think you're pretty stuck with this. VLookUp returns a string, not an object, and that's that.
Not sure why you were looking for the hyperlink attribute from those cells, but you'll simply need to make sure that the cells text values contain what you want. Otherwise, you'll need to do the lookup a different way.
=========================
So your sub becomes (with the PrintFile line commented out)
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 String
Dim List_EquipCert As Range
Dim FilePath As String
'On Error GoTo Err1:
Set List_EquipCert = Range("List_EquipCert")
'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)
'Call PrintFile(FilePath)
Call WaitTime
End If
Next j
Next i
Err1:
End Sub
Regards, Dave S
PS I'll archive your file for a couple of days, then delete it, just in case you have further problems.
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Thursday, 23 February 2017 1:29 PM
To: ExcelVBA@yahoogroups.com
Subject: 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 -----
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 (8) |
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