Vlookup issues VBA

[liam-the-1-n-only]

Good Morning All

Any help with this issue would be greatly appreciated, i cannot seem to figure out why this wont work..?

Dim refcell As String
Dim Location As String
Dim excelname As String
Dim ext As String
Dim r As String
Dim lookuprange As String
Dim cs As Integer
Dim Boo As Boolean
Dim Row As Integer


Row = ActiveCell.Row
refcell = "$A" & Row
Location = "'D:\Documents and Settings\*****\Desktop\vb excel\testing docs\"
Name = "[" & Format(Date, "dd.mm.yy")
ext = ".xlsx]"
excelname = Name & ext
r = "Sheet1'!$A:$AS"
lookuprange = Location & excelname & r
cs = 40
Boo = False

If i do the below i get , "unable to get vlookup property of worksheetfunction class"

ActiveCell.Value = Application.WorksheetFunction.VLookup("refcell", "lookuprange", "cs", "Boo")

If i do this ;

ActiveCell.Formula = "=VLookup(refcell, lookuprange, cs, Boo)"

i get this in the relevant cell, =VLOOKUP(refcell, lookuprange, cs, Boo)

this issue has been causing me hours of headaches and forum posting to no provale so any help would be GREAT!

Thanks
Liam

 

[VoG]

Try

ActiveCell.Value = Application.VLookup(refcell, lookuprange, cs, Boo)

 

[njimack]

Welcome to the board. I'd go for something like this:
ActiveCell.Formula = "=VLOOKUP(" & refcell & "," & lookuprange & "," & cs & "," & Boo & ")"

 

[liam-the-1-n-only]

Try

ActiveCell.Value = Application.VLookup(refcell, lookuprange, cs, Boo)

with this i just get a #Value! error...

 

[liam-the-1-n-only]

Welcome to the board. I'd go for something like this:
ActiveCell.Formula = "=VLOOKUP(" & refcell & "," & lookuprange & "," & cs & "," & Boo & ")"

with this i get a Application-defined or Object-defined error

 

[liam-the-1-n-only]

just to add to the situation as well if i remove the file location it works :/ but only if i move the file to the relative folder location so it seems as if the location string is what is screwing it up, hope this helps..

 

[AlphaFrog]

Cross post
Vlookup to a different spreadsheet VBA