Prev: Equal list values.
Next: Just a test
From: Dave Peterson on 3 Jun 2010 07:10
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
From: Len on 4 Jun 2010 06:33
Hi Dave,

Thanks for your advice


Regards
Len
From: Len on 4 Jun 2010 07:04
Hi,


Thanks to Dave for his kind advice, I manage to obtain the result of
vlookup for cell B3
Now , I modified the codes and wish to copy down vlookup formula based
on adjacent cell ( ie using helper column A ) and this process will
slow down very much when this applies to multiple columns, is there
anyway to speed up the loop in order to achieve the required result

Sub vbVlookup()
Dim strPath As String
Dim strFilename As String
Dim strLookupSheet As String
Dim strLookupRange As String
Dim strLookupValue As String
Dim strLastRow As String
Dim tgLastRow As String
Dim tLrow As Integer
Dim i As Long

    strLookupValue = "A$3"
    strPath = "D:\My Documents\P\ManagementAcct\Apr10\PYY\"
    strFilename = "PYY PL Co compare1.Apr'10.xls"
strFilename = replace(strfilename, "'","''")
    strLookupSheet = "P&L - COMPANY (compare 1)"
    strLookupRange = "$A$3:$O$60"
strLastRow = "B$60"
tgLastRow = "C$60"
    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")
tLrow = .Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To tLrow

If .Sheets(3).Cells(i, 1).Value = "a" Then
.Sheets(3).Cells(3, 3).Formula = "=VLOOKUP( " & strLookupValue
& " ,'" & strPath & "[" & strFilename & "]" & _
strLookupSheet & "'!" & strLookupRange & ", 2,FALSE)"
.Sheets(3).Cells(3, 3).Copy .Sheets(3).Cells(i, 3)
.Sheets(3).Cells(tLrow + 4, 3).Formula = "= '" & strPath & "[" &
strFilename & "]" & _
strLookupSheet & "'!" & strLastRow & "-" & tgLastRow
End If
Next
End With
Workbooks(strFilename).Close savechanges:=False
Application.ScreenUpdating = True

End Sub

Any help will be much appreciated and thanks in advance


Regards
Len

From: Dave Peterson on 4 Jun 2010 07:17
In this loop:

For i = 3 To tLrow

If .Sheets(3).Cells(i, 1).Value = "a" Then
.Sheets(3).Cells(3, 3).Formula _
= "=VLOOKUP( " & strLookupValue & " ,'" & strPath _
& "[" & strFilename & "]" & _
strLookupSheet & "'!" & strLookupRange & ", 2,FALSE)"

.Sheets(3).Cells(3, 3).Copy .Sheets(3).Cells(i, 3)

.Sheets(3).Cells(tLrow + 4, 3).Formula _
= "= '" & strPath & "[" & strFilename & "]" & _
strLookupSheet & "'!" & strLastRow & "-" & tgLastRow
End If
Next i

You're populating the formula in C3 each time. You could move that out of the
loop and just do it once.

You may want to turn calculation off, insert the formulas, and then turn the
calculation on. I'm not sure if that will help, but it can't hurt to test.



Len wrote:
>
> Hi,
>
> Thanks to Dave for his kind advice, I manage to obtain the result of
> vlookup for cell B3
> Now , I modified the codes and wish to copy down vlookup formula based
> on adjacent cell ( ie using helper column A ) and this process will
> slow down very much when this applies to multiple columns, is there
> anyway to speed up the loop in order to achieve the required result
>
> Sub vbVlookup()
> Dim strPath As String
> Dim strFilename As String
> Dim strLookupSheet As String
> Dim strLookupRange As String
> Dim strLookupValue As String
> Dim strLastRow As String
> Dim tgLastRow As String
> Dim tLrow As Integer
> Dim i As Long
>
> strLookupValue = "A$3"
> strPath = "D:\My Documents\P\ManagementAcct\Apr10\PYY\"
> strFilename = "PYY PL Co compare1.Apr'10.xls"
> strFilename = replace(strfilename, "'","''")
> strLookupSheet = "P&L - COMPANY (compare 1)"
> strLookupRange = "$A$3:$O$60"
> strLastRow = "B$60"
> tgLastRow = "C$60"
> 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")
> tLrow = .Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
> For i = 3 To tLrow
>
> If .Sheets(3).Cells(i, 1).Value = "a" Then
> .Sheets(3).Cells(3, 3).Formula = "=VLOOKUP( " & strLookupValue
> & " ,'" & strPath & "[" & strFilename & "]" & _
> strLookupSheet & "'!" & strLookupRange & ", 2,FALSE)"
> .Sheets(3).Cells(3, 3).Copy .Sheets(3).Cells(i, 3)
> .Sheets(3).Cells(tLrow + 4, 3).Formula = "= '" & strPath & "[" &
> strFilename & "]" & _
> strLookupSheet & "'!" & strLastRow & "-" & tgLastRow
> End If
> Next
> End With
> Workbooks(strFilename).Close savechanges:=False
> Application.ScreenUpdating = True
>
> End Sub
>
> Any help will be much appreciated and thanks in advance
>
> Regards
> Len

--

Dave Peterson
From: Len on 6 Jun 2010 01:13
Hi Dave,

With your suggestion, It works fine
Thanks for your advice again

Is there any alternative to speed up the loop ?


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