VBA can't convert Date to string

Question

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

If you mention an error, that's not useful without posting the full text of it, and tell readers what line the IDE highlighted. — underscore_d
– underscore_d, Commented Nov 9, 2017 at 9:50
As @ExcelDevelopers says - you're passing a range of values to the function when it is expecting just one. — Tom
– Tom, Commented Nov 9, 2017 at 9:52
as @ExcelDevelopers mentioned, when using Merged cells, you need to use the reference of the first column. — Shai Rado
– Shai Rado, Commented Nov 9, 2017 at 9:53

Ralph · Accepted Answer · 2017-11-09 10:04:56Z

2

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

answered Nov 9, 2017 at 10:04

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA can't convert Date to string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related