VBA - DateAdd

[george hart]

Hi

The code below isn't working and putting the wrong date in "L"??

I month from the 01/08/2011 shouldn't read 17/08/2011??...any ideas where I'm going wrong?

Dim X As Variant
For X = 3 To Cells(Rows.Count, "L").End(xlUp).Row
ldate = Format(DateAdd("m", Range("M" & X), Range("K" & X)) - Day(Now), "dd/mm/yyyy")
Range("L" & X) = ldate

<TABLE style="WIDTH: 258pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=342 x:str><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 30.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=41 width=61>J</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=65>K</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=66>L</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=69>M</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=81>N</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 46pt; HEIGHT: 22.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=30 width=61>Notification Sent</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=65>Last date of issue</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 50pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=66>Date of next Review</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=69>Review Freq (Months)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 61pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=81>No of Days prior to review:</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=15 x:num="40765">10/08/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num="40756">01/08/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>17/08/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num x:fmla='=IF(B3="","",DAYS360(TODAY(),C3))'>2</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=15 x:num="40765">10/08/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num="40770">15/08/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>30/09/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num x:fmla='=IF(B4="","",DAYS360(TODAY(),C4))'>45</TD></TR></TBODY></TABLE>


Many thanks in advance

 

[Trevor G]

I would imagine you need more than just your example shown. But here is something based on what you are showing.

Excel Workbook
	J	K	L	M	N
1	Notification Sent	Last date of issue	Date of next Review	Review Freq (Months)	No of Days prior to review:
2	10/08/2011	01/08/2011	01/09/2011	1	2
3	10/08/2011	15/08/2011	15/10/2011	2	45
Sheet3

Code

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> try()<br>Range("L2").Value = DateAdd("m", Range("M2"), Range("K2").Value)<br>Range("L3").Value = DateAdd("m", Range("M3"), Range("K3").Value)<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[george hart]

Many thanks

The code below works a treat:


Dim X As Variant
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
For X = 3 To Cells(Rows.Count, "L").End(xlUp).Row
<o></o>
Range("L" & X).Value = DateAdd("m", Range("M" & X), Range("K" & X).Value)
<o></o>
<o></o>
Next
End Sub

 

[Trevor G]

Pleased to read you have a working solution.