datediff help

[ina]

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]

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 lin
entry on your vba module sheet. This will then highlight mistype
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 Variabl
Declaration

When Declaring variables I also use a mix of upper and lower cas
letters I then enter all my macro code in lower case and if I type th
variable name correctly it changes to the exact way I declared i
(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 lik
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

Loo

 

[Ron Coderre]

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

 

[Andrew Taylor]

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.

 

[ina]

Thanks a lot for this help

Ina