Problem with "#N/A" after vlookup

[Les Stout]

Hi all, i am doing a series of Vlookups and if the value is not found i
get "#N/A" which i would like to replace with the text "Not found".
Any help or suggestion would be appreciated

With Cells(4, "I").Resize(numRows)
.Formula = "=VLOOKUP(A4," & myLookUpRng.Address(1, 1, xlA1,
True) & ",19,0)"
.Value = .Value
End With
Thanks in advance

Les Stout

 

[Bob Phillips]

Dim sFormula as string

sFormula = "VLOOKUP(A4," & myLookUpRng.Address(1, 1, xlA1,True) &
",19,0)"
sformula = "=IF(ISNA(" & sformula & "),""""," & sformula & ")"
With Cells(4, "I").Resize(numRows)
.Formula = sformula
.Value = .Value
End With



--

HTH

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

 

[Les Stout]

Thanks Bob, 100%.....

Les Stout

 

[Les Stout]

Hi Bob, below is my final product. I now have to do another 6 look ups
from 6 different workbooks, so i have to include, possibly an If
statement, to not process column "I" if it is greater than 0 or does not
find a value.

Sub LookupAud()

'------ New code puts in a 0 if lookup empty ----------------

Dim myLookUpRng As Range
Dim i As Long
Dim numRows As Long
Dim LastRow As Long
Dim sFormula As String
Range("A4").Select
With Workbooks(myfileNameAud).Worksheets(SheetNameAud)
Set myLookUpRng = .Range("C:U")
End With
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
numRows = LastRow - 3
sFormula = "VLOOKUP(A4," & myLookUpRng.Address(1, 1, xlA1, True) & _
",19,0)"
sFormula = "=IF(ISNA(" & sFormula & "),0," & sFormula & ")"
With Cells(4, "I").Resize(numRows)
.Formula = sFormula
.Value = .Value
.NumberFormat = "#,##0.00"
.Offset(0, 1).NumberFormat = "#,##0.00"
.Offset(0, 2).NumberFormat = "#,##0.00"
End With
Workbooks("Audio.xls").Close
' GetBat
' UpdateProgressV 0.4 '-(i - 3) / numRows
' Range("A4").Select
' InsPriceDiff '--CloseForm2
End Sub

Should i inclued the below to speed up the script, as the sheets can be
very long ?

Worksheets(1).EnableCalculation = True
Worksheets(1).EnableCalculation = False


Les Stout

 

[Bob Phillips]

Les,

Add this to the start of the code

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

then at the end add

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

--

HTH

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

 

[Les Stout]

Thanks for that Bob, will do. Bob, can you help on the rest of my
question ? in that i have to do another 5 lookups, putting the info into
column "I" but not over write the values already put there by the
previouse vlookups.

best regards,

Les Stout

 

[Bob Phillips]

Don't think you can as I read your code Les, As you are dumping the formula
into a batch of cells, you would need to change that to loop and test it for
empty first


--

HTH

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

 

[Tom Ogilvy]

Dim myLookUpRng As Range
Dim i As Long, k as Long
Dim numRows As Long
Dim LastRow As Long
Dim sFormula As String
Dim dblVal as Double
Dim v, v1, arrPrice(1 to 6), arrPrice1
' list of workbook names in order to be checked
v = ("A.xls","B.xls","C.xls","D.xls","E.xls","F.xls")
' list of sheet in that workbook to be checked -
' same order as above
v1 = ("Sh1","Data","Parts","Sheet2","Data","Data")

Range("A4").Select
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
numRows = LastRow - 3
Redim arrPrice1(1 to numRows, 1 to 1)
k= 0
For i = lbound(v) to ubound(v)
k = k + 1
myfileNameAud = v(i)
SheetNameAud = v1(i)
With Workbooks(myfileNameAud).Worksheets(SheetNameAud)
Set myLookUpRng = .Range("C:U")
End With
sFormula = "VLOOKUP(A4," & myLookUpRng.Address(1, 1, xlA1, True) & _
",19,0)"
sFormula = "=IF(ISNA(" & sFormula & "),0," & sFormula & ")"
With Cells(4, "I").Resize(numRows)
.Formula = sFormula
.Value = .Value
arrPrice(k) = .Value
End With
Next i

for i = 1 to numrows
for k = 1 to 6
dblval = arrPrice(k)(i,1)
if dblval <> 0 or k = 6 then
arrPrice1(i,1) = dblVal
exit for
end if
Next
Next

With Cells(4, "I").Resize(numRows)
.Value = ArrPrice1
.NumberFormat = "#,##0.00"
.Offset(0, 1).NumberFormat = "#,##0.00"
.Offset(0, 2).NumberFormat = "#,##0.00"
End With

' Workbooks(Audio).Close
' GetBat
' UpdateProgressV 0.4 '-(i - 3) / numRows
' Range("A4").Select
' InsPriceDiff '--CloseForm2
End Sub

 

[Les Stout]

Tom, i am speechless !!! you sieze to amaze me ...... Thank you so much.

best regards,

Les Stout

 

[Les Stout]

Hi Tom, sorry last question, am i right in assuming that all the
workbooks must be open before the proceedure runs & how many can one do
in in this manner ?

Les Stout

 

[Tom Ogilvy]

Each workbook would need to be open when it was being accessed. You could
add code to open and close then in the loop, but I would try it by just
having them all open.