VBA : Date Format()

Hi there,

See if the following version of your code does what you need:

Option Explicit


Sub weektodata()

    Dim filtrodata2 As String
    Dim filtrodata  As Date
    Dim iWeek       As Integer
    Dim iYear       As Integer
    Dim xDStr       As String
    Dim xDWs        As Worksheet
    Dim xWs         As Worksheet

    xDStr = "Home"

    Set xDWs = Worksheets(xDStr)

    iWeek = xDWs.Cells(2, 4).Value
    iYear = 2019
    filtrodata = DateSerial(iYear, 1, ((iWeek - 1) * 7) + 2 - Weekday(DateSerial(iYear, 1, 1)) + 1)

    filtrodata2 = Format(filtrodata, "dd.MMM.aaaa")
    MsgBox (filtrodata)

End Sub

The real problem in your code is that in the following statement:

filtrodata2 = Format(filtrodata, "dd.MMM.aaaa")

you are attempting to assign a string expression to the variable "filtrodata2" which is declared as a Date variable - the situation is not helped by the fact that the "open-ended" On Error Resume Next statement prevents the "Type Mismatch" error message from giving you an indication as to where the problem is occurring.

Just for information, the statement:

Dim xWs, xDWs As Worksheet

declares xWs as a VARIANT, and xlWs as a worksheet - the same applies to your other Dim statements, i.e. the variables whose types are not explicitly declared are implicitly declared as being of type Variant by default.

Hope this helps - please let me know how you get on.

Regards,

Greg M