英文:
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, ".", "-")))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论