Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stuck with script - Help Please
Thanks a million Bob, have a great evening.
best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am stuck | Excel Worksheet Functions | |||
Im stuck in VB! | Excel Programming | |||
Ok now im stuck | Excel Programming | |||
Still stuck | Excel Programming | |||
Excel 2000/XP script to Excel97 script | Excel Programming |