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

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

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" wrote in message
...
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

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

Thanks Bob, 100%.....

Les Stout

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

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

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

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" wrote in message
...
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

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

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

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