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 *** |
Stuck with script - Help Please
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 *** |
Stuck with script - Help Please
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 *** |
Stuck with script - Help Please
Thanks a million Bob, have a great evening.
best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
Stuck with script - Help Please
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 *** |
Stuck with script - Help Please
Hi Bob, it works great, thanks for all your input.
best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
All times are GMT +1. The time now is 12:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com