I'm not sure what you're doing with that last formula in that loop. (Maybe just

checking????)

But how about:

Option Explicit

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 Long

Dim i As Long

Dim iLoop As Long

Dim rNA As Range

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

With Workbooks("PYY & PHV Monthly analysis- Apr'10.xls")

tLrow = .Sheets(3).Range("A" & Rows.Count).End(xlUp).Row

iLoop = WorksheetFunction.CountIf(.Sheets(3).Columns(1), "a")

Set rNA = .Sheets(3).Range("A1")

'look for all the A's, don't start with 3.

For i = 1 To iLoop

Set rNA = .Sheets(3).Columns(1).Find(What:="a", After:=rNA, _

LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _

SearchDirection:=xlNext, MatchCase:=True)

rNA.Offset(0, 2).Formula = "=VLOOKUP(" _

& rNA.Offset(0, 1).Address(external:=True) _

& " ,'" & strPath & "[" & strFilename & "]" & _

strLookupSheet & "'!" & strLookupRange & ", 2,FALSE)"

.Sheets(3).Cells(tLrow + 4, 3).Formula = "= '" & strPath _

& "[" & strFilename & "]" _

& strLookupSheet & "'!" & strLastRow & "-" & tgLastRow

Next i

End With

Workbooks(strFilename).Close savechanges:=False

Application.ScreenUpdating = True

End Sub

Hi Dave, Don,

Thanks alot and stay with me

Sorry for late reply as I was busy with month end closing

"a" is another lookup string in helper column to assist vlookup

formula ( ie when there is "a" in column A3, vlookup formula will be

placed on cell C3 to lookup value in B3 from excel source file )

After several attempts to workaround, I came up with another codes

below to replace the earlier one that try to achieve the similar

required result

I just explore the way to speed up the loop but it fails to change

lookup value $B3 ( ie relative reference ) when populating the vlookup

formula in C3 each time

Dim k As Long

Dim rNa As Variant

Dim iLoop As Integer

With Workbooks("PYY& PHV Monthly analysis- Apr'10.xls")

tLrow = .Sheets(3).Range("A"& Rows.Count).End(xlUp).Row

iLoop = WorksheetFunction.CountIf(.Sheets(3).Columns(1), "a")

Set rNa = .Sheets(3).Range("A1")

For i = 3 To iLoop

Set rNa = .Sheets(3).Columns(1).Find(What:="a", After:=rNa, _

LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,

_

SearchDirection:=xlNext, MatchCase:=True)

rNa.Offset(0, 2).Formula = "=VLOOKUP( "& strLookupValue&

" ,'"& strPath& "["& strFilename& "]"& _

strLookupSheet& "'!"& strLookupRange& ", 2,FALSE)"

.Sheets(3).Cells(tLrow + 4, 3).Formula = "= '"& strPath& "["

& strFilename& "]"& _

strLookupSheet& "'!"& strLastRow& "-"& tgLastRow

Next i

Any idea how to rectify the above codes and thanks again

Regards

Len