error handling

  • Thread starter Thread starter sidata
  • Start date Start date
S

sidata

I have following code:-

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

where = "E44"
what =
(Abs((WorksheetFunction.Substitute(WorksheetFunction.Substitute(WorksheetFun
ction.Substitute(WorksheetFunction.Substitute(Worksheets("schedule").Range(w
here), "M", ""), "T", ""), "P", ""), " ", ""))))
from = Worksheets("Product").Range("A:L")
res = Application.WorksheetFunction.VLookup(what, from, 2, False)

Range(where).Value = res

End Sub

But need to add error handling, but am having trouble.
If "Range(where).Value = res" is an error of any type i what
Range(where).Value = "Error" else Range(where).Value = res

Any help would be great
Simon
 
Hi
try:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)

where = "E44"
what =
(Abs((WorksheetFunction.Substitute(WorksheetFunction.Substitute(Workshe
etFun
ction.Substitute(WorksheetFunction.Substitute(Worksheets("schedule").Ra
nge(w
here), "M", ""), "T", ""), "P", ""), " ", ""))))
from = Worksheets("Product").Range("A:L")
on error resume next
res = Application.WorksheetFunction.VLookup(what, from, 2, False)
if err.number<>0 then
msgbox "Error"
else
Range(where).Value = res
end if
on error goto 0

End Sub
 
Back
Top