CODEWITHSUNDEEP
arraysexcelvbastring
Nötter
Nötter

Reputation: 5

Add one month to date with format "31.01.2023"

I want to add a month to a date. The DateAdd function works, till I use it in a for-loop and pass date from an array then I get a type mismatch error.

Public Function copyWiederkehrend(all As Variant)
    
    Dim month As String
    Dim datePay As Date
    Dim i, j As Integer
    Dim ErsteLeereZeile As Long
    
    'loop throw all rows that i have passed
    For i = 1 To all.Rows.Count
        
        'looking for the first emtpy row
        ErsteLeereZeile = Worksheets("Zusammenzug").Cells(Rows.Count, 1).End(xlUp).row + 1
        'shows the hidden column copy the row and remove the column it again
        Columns("C:C").EntireColumn.Hidden = False
        range("B" & all(i).row & ":L" & all(i).row).Copy
        Columns("C:C").EntireColumn.Hidden = True
        With ThisWorkbook.Worksheets("Zusammenzug")
            'add the first row
            .Cells(ErsteLeereZeile, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            'copy the added row and add it 17 times again and add one month
            For j = 1 To 17
                ErsteLeereZeile = Worksheets("Zusammenzug").Cells(Rows.Count, 1).End(xlUp).row + 1
                .Cells(ErsteLeereZeile, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                month = .range("K" & ErsteLeereZeile - 1).Value
                datePay = DateAdd("m", 1, month)
                .range("K" & ErsteLeereZeile).Value = datePay
            Next j
        End With
    Next i
End Function

My dates have a format "31.01.2023".

First I thought it was because its not yyyy-mm-dd but if I replace:

month = .range("K" & ErsteLeereZeile - 1).Value
               datePay = DateAdd("m", 1, month)

with:

datePay = DateAdd("m", 1, "31.01.2023")

It works (without adding the month because its hardcoded).

I tried it in debug mode and I'm 100% sure that the string I pass to the dateadd function is "31.01.2023".

The short version:
I have a cell with a date like 31.12.2013 as string and I want to add one month.

stringMonth = ActiveSheet.range("K" & ErsteLeereZeile - 1).Value
dateMonth   = DateAdd("M", 1, CDate(stringMonth))

I get a type mismatch error.

Upvotes: 0

Views: 118

Answers (2)

CDP1802
CDP1802

Reputation: 16174

If it is a string then split into parts and use DateSerial(y,m,d).

Dim s As String, d As Date, a As Variant
s = "31.12.2013"
a = Split(s, ".")
d = DateAdd("m", 1, DateSerial(a(2), a(1), a(0)))
Debug.Print d

Upvotes: 0

CHill60
CHill60

Reputation: 1958

If I use dateMonth = DateAdd("M", 1, "31.01.2023") I get a Type Mismatch error. I also get the same error with dateMonth = DateAdd("M", 1, CDate(stringMonth)) It is because Excel is not recognising 31.01.2023 as a string that represents a date.

Try replacing the periods with hyphens e.g.

dateMonth = DateAdd("M", 1, CDate(Replace(stringMonth, ".", "-")))

Upvotes: -1

Related Questions