Dim myLookUpRng As Range
Dim i As Long, k as Long
Dim numRows As Long
Dim LastRow As Long
Dim sFormula As String
Dim dblVal as Double
Dim v, v1, arrPrice(1 to 6), arrPrice1
' list of workbook names in order to be checked
v = ("A.xls","B.xls","C.xls","D.xls","E.xls","F.xls ")
' list of sheet in that workbook to be checked -
' same order as above
v1 = ("Sh1","Data","Parts","Sheet2","Data","Data")

Range("A4").Select
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
numRows = LastRow - 3
Redim arrPrice1(1 to numRows, 1 to 1)
k= 0
For i = lbound(v) to ubound(v)
k = k + 1
myfileNameAud = v(i)
SheetNameAud = v1(i)
With Workbooks(myfileNameAud).Worksheets(SheetNameAud)
Set myLookUpRng = .Range("C:U")
End With
sFormula = "VLOOKUP(A4," & myLookUpRng.Address(1, 1, xlA1, True) & _
",19,0)"
sFormula = "=IF(ISNA(" & sFormula & "),0," & sFormula & ")"
With Cells(4, "I").Resize(numRows)
.Formula = sFormula
.Value = .Value
arrPrice(k) = .Value
End With
Next i

for i = 1 to numrows
for k = 1 to 6
dblval = arrPrice(k)(i,1)
if dblval < 0 or k = 6 then
arrPrice1(i,1) = dblVal
exit for
end if
Next
Next

With Cells(4, "I").Resize(numRows)
.Value = ArrPrice1
.NumberFormat = "#,##0.00"
.Offset(0, 1).NumberFormat = "#,##0.00"
.Offset(0, 2).NumberFormat = "#,##0.00"
End With

' Workbooks(Audio).Close
' GetBat
' UpdateProgressV 0.4 '-(i - 3) / numRows
' Range("A4").Select
' InsPriceDiff '--CloseForm2
End Sub

--
Regards,
Tom Ogilvy


"Les Stout" wrote in message
...
Thanks for that Bob, will do. Bob, can you help on the rest of my
question ? in that i have to do another 5 lookups, putting the info into
column "I" but not over write the values already put there by the
previouse vlookups.

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***