Sub LookupBat()

Dim myLookUpRng As Range
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long
Dim sFormula As Integer
Range("A4").Select
' UpdateProgressH = 0
With Workbooks(myfileNameBat).Worksheets(SheetNameBat)
Set myLookUpRng = .Range("C:U")
End With
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
NumRows = LastRow - 3
For i = 4 To LastRow
With Cells(i, "I")
If .Value = "" Then
.Value = Application.VLookUp(.Value, _
myLookUpRng, 19, 0)
If IsError(.Value) Then
.Value = ""
Else
.Value = .Value
End If
End If
End With
UpdateProgressH (i - 3) / NumRows
Next i
Unload UserForm2
Range("A4").Select
' InsPriceDiff
End Sub


--

HTH

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


"Les Stout" wrote in message
...
Hi All, i have the script below that works fine with one exception and
that is that it puts an "#N/A" in the cell and i would like it to be
blank.

Sub LookupBat()

Dim myLookUpRng As Range
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long
Dim sFormula As Integer
Range("A4").Select
' UpdateProgressH = 0
With Workbooks(myfileNameBat).Worksheets(SheetNameBat)
Set myLookUpRng = .Range("C:U")
End With
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
NumRows = LastRow - 3
For i = 4 To LastRow
If Cells(i, "I") = "" Then
Cells(i, "I").Value = Application.VLookUp(Cells(i,
"A").Value, _
myLookUpRng, 19, 0)
Cells(i, "I").Value = Cells(i, "I").Value
End If
UpdateProgressH (i - 3) / NumRows
Next i
Unload UserForm2
Range("A4").Select
' InsPriceDiff
End Sub


Thanks in advance,

Les Stout

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