Stuck with script - Help Please

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" <anonymous@devdex.com> wrote in message
news:OmvcR1I4FHA.4076@TK2MSFTNGP15.phx.gbl...
> 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" <anonymous@devdex.com> wrote in message
news:e0wExDJ4FHA.3460@TK2MSFTNGP12.phx.gbl...
> 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 ***