Les,

Add this to the start of the code

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

then at the end add

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Les Stout" wrote in message
...
Hi Bob, below is my final product. I now have to do another 6 look ups
from 6 different workbooks, so i have to include, possibly an If
statement, to not process column "I" if it is greater than 0 or does not
find a value.

Sub LookupAud()

'------ New code puts in a 0 if lookup empty ----------------

Dim myLookUpRng As Range
Dim i As Long
Dim numRows As Long
Dim LastRow As Long
Dim sFormula As String
Range("A4").Select
With Workbooks(myfileNameAud).Worksheets(SheetNameAud)
Set myLookUpRng = .Range("C:U")
End With
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
numRows = LastRow - 3
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
.NumberFormat = "#,##0.00"
.Offset(0, 1).NumberFormat = "#,##0.00"
.Offset(0, 2).NumberFormat = "#,##0.00"
End With
Workbooks("Audio.xls").Close
' GetBat
' UpdateProgressV 0.4 '-(i - 3) / numRows
' Range("A4").Select
' InsPriceDiff '--CloseForm2
End Sub

Should i inclued the below to speed up the script, as the sheets can be
very long ?

Worksheets(1).EnableCalculation = True
Worksheets(1).EnableCalculation = False


Les Stout

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