Excel VBA CDate Type Mismatch Error

[Biz]

Dear All,

I have compile a code it works well except when Text is evaluated, which are not dates like "Weekly" and "MTD".
Line in bold red gives type mismatch error. Your help would be greatly appreciated.

Sub Add_DataFields(pt As PivotTable, rng As Range)    Dim ptField As PivotField, ptItem As PivotItem
    Dim vPTDataFields, sField
    Dim i As Long
    
    '~~~> Convert 1D to 2D
    vPTDataFields = Application.Transpose(rng.Value)
    
    '~~~> Loop Thru Data Fields
    For i = LBound(vPTDataFields) To UBound(vPTDataFields)
        
        sField = [COLOR=#FF0000][B]IIf(Not (IsError((CDate((vPTDataFields(i, 1)))))), Format(vPTDataFields(i, 1), "ddd dd/mm/yyyy"), vPTDataFields(i, 1))[/B][/COLOR]
        'MsgBox IIf(Application.IsNumber(vPTDataFields(i, 1)), Format(vPTDataFields(i, 1), "ddd mmm yyyy"), vPTDataFields(i, 1)), vbInformation
        Debug.Print "For array element " & i & " the number is " & vPTDataFields(i, 1)
       ' pt.AddDataField pt.PivotFields("Thu 24/11/2016"), "Sum of Thu 24/11/2016", xlSum
        pt.AddDataField pt.PivotFields(sField), "Sum of " & sField, xlSum
    Next i
    
    Set pt = Nothing
    
End Sub

Biz

 

[Jerry Sullivan]

Hi Mate,

Conversion functions will generate a runtime error even if they are wrapped in an IsError() function.

Have you tried testing your non-date field names with this...
sField = Format(vPTDataFields(i, 1), "ddd dd/mm/yyyy")

That Format expression will return "MTD" and "Weekly" from those inputs.

If that doesn't work and if you know that your valid dates are of a format that will return True when evaluated by IsDate(), then you could use this...

sField = IIf(IsDate(vPTDataFields(i, 1)),....

That would work for input expressions like "12/10/2016", but not for serial values like 42714.

If you need to handle serial values, then try calling a function that has an error handler.

Function bIsDate(vInput As Variant) As Boolean
 On Error Resume Next
 bIsDate = Not IsError(CDate(vInput))
End Function

Function call...

sField = IIf(bIsDate(vPTDataFields(i, 1)),....

 

[Biz]

Hi Jerry,

The UDF worked like a charm. Thank you for your help.
Merry Xmas and Happy New Year.

Kind Regards

Biz