“31.01.2023” 加一個月。

huangapple go评论126阅读模式
英文:

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.

英文:

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.

答案1

得分: 0

如果它是一个字符串,然后分割成部分并使用 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
英文:

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

答案2

得分: -1

如果我使用dateMonth = DateAdd("M", 1, "31.01.2023"),我会得到一个类型不匹配的错误。使用dateMonth = DateAdd("M", 1, CDate(stringMonth)) 也会得到相同的错误。这是因为Excel无法识别"31.01.2023"作为表示日期的字符串。

尝试用连字符替换句点,例如:

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

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, ".", "-")))

huangapple
  • 本文由 发表于 2023年3月7日 23:12:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/75663764.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定