Option Explicit
Sub TryIt()
'
'******************************************************************************
' Name : TryIt / erstellt : 27.11.2014 / 13:42 / Sub
'------------------------------------------------------------------------------
'
' Fehlerquellen WorksheetFunction.Match
'
Const sMSG As String = "Wert nicht gefunden,oder,Lookup_array Text formatiert,oder,Lookup_value kein IEEE 64-bit (8-byte)"
'******************************************************************************
'
Dim dbl_Zeile As Long
Dim dat_CDS_Datum As Date
Dim aMsg() As String
'
On Error GoTo TryIt_Error
'
dat_CDS_Datum = Date - 55 'example
'
dbl_Zeile = Application.WorksheetFunction.Match(CDbl(dat_CDS_Datum), Worksheets("Customer_Delivery_Summary").Range("CDS_WEEK"), 0)
'dbl_Zeile = WorksheetFunction.Match(CDbl(dat_CDS_Datum), Range("CDS_WEEK"), 0) '<< wenn Workbook.Names !!!
'
On Error GoTo 0
'
TryIt_Error:
'------------------------------------------------------------------------------
Select Case Err.Number
Case Is = 0
Call MsgBox(CStr(dbl_Zeile), vbInformation, "success")
Case Is = 1004
aMsg = Split(sMSG, ",")
Call MsgBox(Join(aMsg, Chr(10)), vbExclamation, "not found")
Case Else
Call MsgBox(Err.Description, vbCritical, Err.Number)
End Select
'------------------------------------------------------------------------------
'
End Sub
|