Predict Next Date

[bijan]

Hi all,
I have a date and I am going to perdict the sixth mounths next in futuer for
10 times,for example the beginig date is 2008/09/28 and I need a VBA code
create next Dates(for 10 times and variable) and Days differences like this:
__A__ __B__
1 2008/09/28
2 2009/03/28 181.00
3 2009/09/28 184.00
4 2010/03/28 181.00
5 2010/09/28 184.00
6 2011/03/28 181.00
7 2011/09/28 184.00
8 2012/03/28 182.00
9 2012/09/28 184.00
10 2013/03/28 181.00

Thanks in advance
Bijan

 

[Gary''s Student]

With 2008/09/28 in B1, in B2 enter:

=DATE(YEAR(B1),MONTH(B1)+6,DAY(B1)) and copy down

 

[bijan]

Hi
Thanks for immediate response, But it doesn't work I prefer do it in a macro
and pass variable instead of 10
Thanks
Bijan

 

[Gary''s Student]

Sub bj()
Dim n As Long, m As Long, i As Long
Dim d As Date
n = 10
m = Cells(Rows.Count, "B").End(xlUp).Row
d = Cells(m, "B").Value
For i = 1 To n
Cells(i + m, "B").Value = DateSerial(Year(d), Month(d) + i * 6, Day(d))
Next
End Sub

you can always change the 10

 

[bijan]

Hi
I run the Macro but i get an error(type mismatch) in line:
d = Cells(m, "B").Value
Thanks
Bijan

 

[Gary''s Student]

1. it needs atleast one starter date in column B
2. the dates in the column must be true dates and not text.

 

[Rick Rothstein]

Here is an alternative for you to try...

Sub SixMonthDates(BeginDate As Date, HowMany As Long, _
Optional CellReference As Variant)
Dim CellRef As Range
If IsMissing(CellReference) Then Set CellRef = ActiveCell
If VarType(CellReference) = vbString Then
Set CellRef = Range(CellReference)
Else
Set CellRef = CellReference
End If
With CellRef
.Value = BeginDate
If HowMany > 1 Then
.Offset(1).Value = DateAdd("m", 6, BeginDate)
.Offset(1, 1).Formula = "=" & .Offset(1).Address(0, 0) & _
"-" & .Address(0, 0)
If HowMany > 2 Then
.Resize(2).AutoFill .Resize(HowMany)
.Offset(1, 1).AutoFill .Offset(1, 1).Resize(HowMany - 1)
End If
End If
End With
End Sub

Call this subroutine from your macro passing it the beginning date, and how
many six month periods you want to display. You can also optionally pass it
the cell reference either as a string address, such as "A5", or as an range,
such as Range("B6")... this would be the cell where the begin date will be
placed. If you omit the 3rd argument (the CellReference), then the code will
default to the ActiveCell.

 

[Rick Rothstein]

No need to leave the formulas for the subtraction in. Here is modified code
that will leave only values in the cells...

Sub SixMonthDates(BeginDate As Date, HowMany As Long, _
Optional CellReference As Variant)
Dim CellRef As Range
If IsMissing(CellReference) Then Set CellRef = ActiveCell
If VarType(CellReference) = vbString Then
Set CellRef = Range(CellReference)
Else
Set CellRef = CellReference
End If
With CellRef
.Value = BeginDate
If HowMany > 1 Then
.Offset(1).Value = DateAdd("m", 6, BeginDate)
.Offset(1, 1).Formula = "=" & .Offset(1).Address(0, 0) & _
"-" & .Address(0, 0)
If HowMany > 2 Then
.Resize(2).AutoFill .Resize(HowMany)
.Offset(1, 1).AutoFill .Offset(1, 1).Resize(HowMany - 1)
.Offset(1, 1).Resize(HowMany - 1).Value = _
.Offset(1, 1).Resize(HowMany - 1).Value
End If
End If
End With
End Sub