vlookup error

chris2727272727

Board Regular
Joined
Jul 10, 2005
Messages
152
e = ("\\office_server\my documents\thames valley\[thames valley database.xls]employee contacts!$b$16:$i$137")


Range("d14").Value = Application.WorksheetFunction.VLookup("97324", e, 3, False)
Range("e14").Value = Application.WorksheetFunction.VLookup("97324", e, 3, False)
Range("b18").Value = Application.WorksheetFunction.VLookup("97324", e, 3, False)
Range("c18").Value = Application.WorksheetFunction.VLookup("97324", e, 3, False)
Range("d18").Value = Application.WorksheetFunction.VLookup("97324", e, 3, False)
Range("e18").Value = Application.WorksheetFunction.VLookup("97324", e, 3, False)
Range("f18").Value = Application.WorksheetFunction.VLookup("97324", e, 3, False)
Range("d14").Value = Application.WorksheetFunction.VLookup("97324", e, 3, False)


i have the above code but when it runs the below error comes up why is that

run time eroor '1004' unable to get the vlookup property of the worksheetfunction class
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
What would the same formulas return if entered on a worksheet?
 
Upvote 0
vlookup

when entered in the sheet it links to the closed worksbook and works ok, obvoisuly the link is writted in full instead of the e variable
 
Upvote 0
#ref problem

i have a worksheet with six sheets all have the same info the trouible is when my vba deletes the code on sheet 1 all the other sheets come up with #ref on that correspondiong line (I use pastelink )
 
Upvote 0
The workbook is closed?

That might be what is causing the problem.

You can use worksheet functions in VBA but they don't handle errors well.

For example if the Vlookup returned an #N/A then that would cause the error you mentioned.

What are you actually trying to do?

Can you not open the workbook?

There are various ways to extract data from closed workbooks but the easiest is probably to just open the workbook, do what you want, then close it.

All that can be done without the user noticing.
 
Upvote 0
different problem

sorry norie

the last two posts relate to two different problems the vlookuo problem is different to the #ref probem


The #ref prolem relates only to one workbook
 
Upvote 0
Chris

My last response was to your first one, if you know what I mean

As to your 2nd post, you mention deleting code? Are you actually deleting VBA code or using it to delete sheets/ranges?
 
Upvote 0
i have vba code on sheet 1 that loops through and were applicable deltes a whole row as mentioned all sheets are linked withe pastelink but instead of deleting #ref is shown in the row that should of been deleted
 
Upvote 0
Chris

Even without using code you'll get #ref errors when you delete rows.

Perhaps you could explain the overall purpose of what you are trying.
 
Upvote 0
well i have seven sheets the master sheet is sheet 1 all the other sheets have the same data which was put there by pastelink what i want is when the code deletes a row on sheet 1 it deletes that same row on sheet 2 sheet 3 sheet4 sheet5 and sheet6.

The only problem might be that row 1 on sheet1 might be on row 3 on sheet2 and row 5 on sheet 3 etc etc
 
Upvote 0

Forum statistics

Threads
1,224,265
Messages
6,177,538
Members
452,782
Latest member
ZCapitao

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top