Prev: Equal list values.
Next: Just a test
From: Len on 1 Jun 2010 03:37
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
From: Jacob Skaria on 1 Jun 2010 05:57
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
> .
>
From: Len on 2 Jun 2010 01:15
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
From: Dave Peterson on 2 Jun 2010 07:03
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
From: Len on 2 Jun 2010 21:16
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



 |  Next  |  Last
Pages: 1 2 3
Prev: Equal list values.
Next: Just a test