CODEWITHSUNDEEP
vbaexcel
Patrick Bateman
Patrick Bateman

Reputation: 271

Excel VBA Month function throwing type mismatch

Here is my data in the A column:

2013-SEP-04 10:51:42
2013-OCT-23 08:58:35
2013-OCT-23 08:58:35
2013-SEP-23 08:58:35
2013-OCT-23 08:58:35
2013-OCT-23 08:58:35
2013-SEP-23 08:58:35

Here is my code:

Sub getMonthNumber()
'
' getMonthNumber()
'

'

    Dim rowsMonth As Long
    Dim todaysMonth As Integer
    Dim todaysMonthDate As Date
    Dim column As Range, cell As Range

    Set column = Range("A1:A" & ActiveSheet.UsedRange.Rows.Count)
    inactiveStaffConnected = 0
    todaysMonth = Month(Date) - 1
    rowsMonth = 0

    For Each cell In column
        rowsMonth = Month(Mid(cell.Value, 10, 2) & Mid(cell.Value, 6, 3) & Mid(cell.Value, 1, 4))

    Next


End Sub

1) Why the mismatch? The Month function works fine in this setup when using it straight in excel rather than a VBA script.

2) Is there a better way to parse the text into a date? The date string is too out of format with what excel considers a date which is why i'm issuing multiple mids.

Upvotes: 0

Views: 4094

Answers (2)

Raghavendra
Raghavendra

Reputation: 3580

what is the cell format of column A.

if it is in general format means this will work

k = "2013-SEP-04 10:51:42" l2 = Month(DateValue(Format(k, "YYYY-MMMM-dd")))

if not means contact me again with the format you have in the excel.

Upvotes: 0

rory.ap
rory.ap

Reputation: 35318

The Month() function in VBA takes a date and just returns the numeric (integer) month (e.g. 1 if you pass it 1/5/2013 4:30 PM). You are getting a type mismatch because you're trying to pass the month abbreviation, e.g. "SEP", to the Month() function when it's actually looking for an argument with a date type, not a string.

You can convert your values into a date using the CDate() function:

MsgBox CDate("2013-SEP-23 08:58:35")

displays "9/23/2013 8:58:35 AM"

Upvotes: 2

Related Questions