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 ***

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 ***

Sorry a correction to that it is putting "#NAME?" into the cell now, but
not overwriting the existing info.

Les Stout

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

Thanks a million Bob, have a great evening.

best regards,

Les Stout

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

Does the other solution not work regardless?

Actually, this means that the formula has a mis-spelt function, or refers to
an object (worksheet, name) that does not exist.

--

HTH

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


"Les Stout" wrote in message
...
Sorry a correction to that it is putting "#NAME?" into the cell now, but
not overwriting the existing info.

Les Stout

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

Hi Bob, it works great, thanks for all your input.

best regards,

Les Stout

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