Hi,

After running the codes below, it prompts error message Application
defined or object defined error and unable to solve : -

Codes extract

Sub vbVlookup()
Dim strPath As String
Dim strFilename As String
Dim strLookupSheet As String
Dim strLookupRange As String
Dim strLookupValue As String



strLookupValue = "A$3"
strPath = "D:\My Documents\P\ManagementAcct\Apr10\PYY\"
strFilename = "PYY PL Co compare1.Apr'10.xls"
strLookupSheet = "P&L - COMPANY (compare 1)"
strLookupRange = "A3:O60"
Application.ScreenUpdating = False
Workbooks.Open strPath & strFilename 'you should also trap the
case where the book is already open.

With Workbooks("PYY & PHV Monthly analysis- Apr'10.xls")
.Sheets(3).Range("B3").Formula = "=VLOOKUP(""" & strLookupValue &
""", " & "'" & strPath & "[" & strFilename & "]" & strLookupSheet &
"'!" & strLookupRange & ", 2, False)"
.Sheets(3).Range("B3").Value = .Sheets(3).Range("B3").Value
'End With
Workbooks(strFilename).Close savechanges:=False
Application.ScreenUpdating = True


End Sub

Appreciate any help

Thanks & Regards
Len

Try

..Sheets(3).Range("B3").Formula = "=VLOOKUP(" & strLookupValue & "," & _
"'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" & _
strLookupRange & ", 2,False)"

--
Jacob (MVP - Excel)


"Len" wrote:

Hi,

After running the codes below, it prompts error message Application
defined or object defined error and unable to solve : -

Codes extract

Sub vbVlookup()
Dim strPath As String
Dim strFilename As String
Dim strLookupSheet As String
Dim strLookupRange As String
Dim strLookupValue As String



strLookupValue = "A$3"
strPath = "D:\My Documents\P\ManagementAcct\Apr10\PYY\"
strFilename = "PYY PL Co compare1.Apr'10.xls"
strLookupSheet = "P&L - COMPANY (compare 1)"
strLookupRange = "A3:O60"
Application.ScreenUpdating = False
Workbooks.Open strPath & strFilename 'you should also trap the
case where the book is already open.

With Workbooks("PYY & PHV Monthly analysis- Apr'10.xls")
.Sheets(3).Range("B3").Formula = "=VLOOKUP(""" & strLookupValue &
""", " & "'" & strPath & "[" & strFilename & "]" & strLookupSheet &
"'!" & strLookupRange & ", 2, False)"
.Sheets(3).Range("B3").Value = .Sheets(3).Range("B3").Value
'End With
Workbooks(strFilename).Close savechanges:=False
Application.ScreenUpdating = True


End Sub

Appreciate any help

Thanks & Regards
Len
.

Jacob,

Thanks for your help, it's still the same error message after replaced
and it is quite difficult to get the correct syntax on

..Sheets(3).Range("B3").Formula = "=VLOOKUP(" & strLookupValue & "," &
_
"'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" &
_
strLookupRange & ", 2,False)"

After several attempts, it fails again

Please help

Thanks

Regards
Len

Since the filename contains an apostrophe, you'll want to modify the string:

strFilename = "PYY PL Co compare1.Apr'10.xls"
Add another line right after it:
strFilename = replace(strfilename, "'","''")

If this doesn't work, then build the formula in a worksheet cell that works.
Just do it manually.

Then share that formula in your followup post.

You're be trying to build that same string in code. And knowing a formula
string that works will help any responder.



Len wrote:

Jacob,

Thanks for your help, it's still the same error message after replaced
and it is quite difficult to get the correct syntax on

.Sheets(3).Range("B3").Formula = "=VLOOKUP(" & strLookupValue & "," &
_
"'" & strPath & "[" & strFilename & "]" & strLookupSheet & "'!" &
_
strLookupRange & ", 2,False)"

After several attempts, it fails again

Please help

Thanks

Regards
Len

--

Dave Peterson

Hi Dave,

Thanks for your reply and your advice

It works perfectly after adding another line of code to replace
apostrophe
Now, I noted that file name and even path name should not contain
apostrophe or may be special character in vba codes


Cheers
Len

The apostrophe is a valid character in paths and filenames.

I think I'd use code to adjust the string rather than forcing users to remember
rules.

But, like you, I don't use them in my path's, filenames, or even sheet names!

Len wrote:

Hi Dave,

Thanks for your reply and your advice

It works perfectly after adding another line of code to replace
apostrophe
Now, I noted that file name and even path name should not contain
apostrophe or may be special character in vba codes

Cheers
Len

--

Dave Peterson