• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

Vlookup error

Hi,

Im getting a syntax error on the below lookup i have created

Can anyone advise why this is so?

Thanks
Seamus





Function test()
Dim Res As Variant
Dim r As Long

r = ActiveCell

Res = Application.WorksheetFunction.VLookup(r,Phoenix Pivot!',A:A,1, 0)


End Function
0
Seamus2626
Asked:
Seamus2626
1 Solution
 
RunriggerCommented:
Phoenix Pivot!',A:A

should be

'Phoenix Pivot'!A:A
0
 
Seamus2626Author Commented:
Still getting a Syntax error

Seamus

Function test()
Dim Res As Variant
Dim r As Long

r = ActiveCell

Res = Application.WorksheetFunction.VLookup(r,'Phoenix Pivot'!A:A,1, 0)


End Function
0
 
NorieVBA ExpertCommented:
Try this.
Res = Application.WorksheetFunction.VLookup(r.Value,Worsheets("Phoenix Pivot").Range("A:A"),1, 0)

Open in new window

0
 
John EastonDirectorCommented:
The bit A:A,1 will generate an error.  Normally you would have a cell reference, e.g:  A1:A10.
0
 
Seamus2626Author Commented:
Thats the one,

Thanks
Seamus
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now