datediff help

Hello,

I have this sub which needs to give me each end of month between the
first date and now


Dim diffdatemonth()
dim currentdate, todaydate as date
dim currentmonth, todaymonth, currentyear as date
dim inceptiondate as date
dim difference as string
dim i as integer
dim datedifference as integer

inceptiondate = "2006/01/06"

todaydate = Now()


currentdate = DateSerial(Year(inceptiondatedate), Month(inceptiondate)
+ 1, 0)
' the current date it is the first end of month so here I would like to
have 2006/01/31

currentmonth = Month(currentdate)
todaymonth = Month(todaydate)

datedifference = 1

i = 1



'until the difference beetween currentdate and todaydate it is greater
than 0


Do While datedifference > 0

currentmonth = Month(currentdate)
currentyear = Year(currentdate)


difference = datediff("d", todaydate, currentdate)

datedifferencee = CInt(difference)

currentdate = DateSerial(currentyear, (currentmonth + i), 1)

difference = datediff("m", todaydate, currentdate)

datedifference = CInt(difference)

currentmonth = currentmonth + 1

Loop


End Sub

I have something wrong in this code and I do not understand why; could
someone to help me

Ina

ina

Change

Dim currentmonth, todaymonth, currentyear As Date
to
Dim currentmonth, todaymonth, currentyear As Integer

Also you have a couple of typos with your variables within your code- they are not the same as what you declared.

To force variable declartion place this OPTION EXPLICIT as the 1st line entry on your vba module sheet. This will then highlight mistyped variables when you try to run it or use the Debug option.

You can also turn this option on for all new workbooks by Tools Menu > Options > Editor Tab and place a tick in the Require Variable Declaration

When Declaring variables I also use a mix of upper and lower case letters I then enter all my macro code in lower case and if I type the variable name correctly it changes to the exact way I declared it (lower and upper case letters)


try this code which I made a couple of changes to

dim diffdatemonth()
Dim currentdate, todaydate As Date
'Dim currentmonth, todaymonth, currentyear As Date
Dim currentmonth, todaymonth, currentyear As Integer
Dim inceptiondate As Date
Dim difference As String
Dim i As Integer
Dim datedifference As Integer

inceptiondate = "2006/01/06"

todaydate = Now()


currentdate = DateSerial(Year(inceptiondate), Month(inceptiondate) + 1, 0)
' the current date it is the first end of month so here I would like to
'have 2006 / 1 / 31

currentmonth = Month(currentdate)
todaymonth = Month(todaydate)

datedifference = 1

i = 1

'until the difference beetween currentdate and todaydate it is greater
'than 0
Do While datedifference > 0

currentmonth = Month(currentdate)
currentyear = Year(currentdate)


difference = DateDiff("d", todaydate, currentdate)

datedifference = CInt(difference)

currentdate = DateSerial(currentyear, (currentmonth + i), 1)

difference = DateDiff("m", todaydate, currentdate)

datedifference = CInt(difference)

currentmonth = currentmonth + 1

Loop

I'm not sure where you're going with your code, but wouldn't something like
this be easier?

'---beginning of code----
Option Explicit

Sub CalcEOMths()
Dim intCtr As Integer
Dim intMthNum As Integer
Dim intYrNum As Integer
Dim dtCalcd As Date

Const dtStartDate As Date = #1/6/2006#

intMthNum = Month(dtStartDate)
intYrNum = Year(dtStartDate)

intCtr = 1
dtCalcd = DateSerial(Year:=intYrNum, Month:=intMthNum + intCtr, Day:=1) - 1
Do Until dtCalcd > Date
MsgBox Format(dtCalcd, "mm/dd/yyyy")
intCtr = intCtr + 1
dtCalcd = DateSerial(Year:=intYrNum, Month:=intMthNum + intCtr, Day:=1) - 1
Loop

MsgBox "done"
End Sub
'---end of code----

Does that give you something to work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"ina" wrote:

> Hello,
>
> I have this sub which needs to give me each end of month between the
> first date and now
>
>
> Dim diffdatemonth()
> dim currentdate, todaydate as date
> dim currentmonth, todaymonth, currentyear as date
> dim inceptiondate as date
> dim difference as string
> dim i as integer
> dim datedifference as integer
>
> inceptiondate = "2006/01/06"
>
> todaydate = Now()
>
>
> currentdate = DateSerial(Year(inceptiondatedate), Month(inceptiondate)
> + 1, 0)
> ' the current date it is the first end of month so here I would like to
> have 2006/01/31
>
> currentmonth = Month(currentdate)
> todaymonth = Month(todaydate)
>
> datedifference = 1
>
> i = 1
>
>
>
> 'until the difference beetween currentdate and todaydate it is greater
> than 0
>
>
> Do While datedifference > 0
>
> currentmonth = Month(currentdate)
> currentyear = Year(currentdate)
>
>
> difference = datediff("d", todaydate, currentdate)
>
> datedifferencee = CInt(difference)
>
> currentdate = DateSerial(currentyear, (currentmonth + i), 1)
>
> difference = datediff("m", todaydate, currentdate)
>
> datedifference = CInt(difference)
>
> currentmonth = currentmonth + 1
>
> Loop
>
>
> End Sub
>
> I have something wrong in this code and I do not understand why; could
> someone to help me
>
> Ina
>
>

mudraker wrote:
> ina
>
> Change
>
> Dim currentmonth, todaymonth, currentyear As Date
> to
> Dim currentmonth, todaymonth, currentyear As Integer
>

Careful: in both cases these lines declare the first two
variables as Variants and only the third as the specified
type.


>> rest snipped

Thanks a lot for this help

Ina