CODEWITHSUNDEEP
vbaexcel
d3rdon
d3rdon

Reputation: 287

VBA can't convert Date to string

I have an excel sheet, which calculates me the current Sunday inside the cell.

I use macros to manipulate the data inside the sheets. But I can't read out the date and convert it to a string.

Run-time error '13': Type mismatch. On the line sDate =

Sub Dateit()

    Dim sDate As String
    'Formula inside cell Q5: =Today()
    'Formula inside merged cells Q6:R6: =INT((Q5-1)/7)*7+1
    'Shows the String: 5-Nov-2017 in cells q6:r6

    sDate = Format(Range("Q6:R6").Value, "dd. mmm yyyy")
    MsgBox sDate

End Sub

Upvotes: 1

Views: 1290

Answers (1)

Ralph
Ralph

Reputation: 9434

If you have multiple values / dates as in your example then I'd recommend that you work with an array like so:

Option Explicit

Sub Dateit()

Dim sDate() As Variant
Dim x As Long, y As Long

'Load all dates into an array
sDate() = ActiveSheet.Range("Q6:R6").Value

'Iterate through all the "rows" of the array (dimension 1)
For y = LBound(sDate, 1) To UBound(sDate, 1)
    'Iterate through all the "columns" of the array (dimension 2)
    For x = LBound(sDate, 2) To UBound(sDate, 2)
        'refomrat the values as dates
        sDate(y, x) = Format(sDate(y, x), "dd. mmm yyyy")
        'show the dates in a message box
        MsgBox sDate(y, x)
    Next x
Next y

End Sub

Upvotes: 2

Related Questions