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 ***

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)


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

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

--
Regards,
Tom Ogilvy


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

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

best regards,

Les Stout

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

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

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

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.

--
Regards,
Tom Ogilvy


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

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