Macro: display dates between start & end date

[Suzanne]

Hello,

Does anyone know how to write a macro that will automatically fill the
dates in a column (starting in cell A2) on sheet2 between the start
date (cell A3) and end date (cell A$) which are entered into a
parameters sheet? So that it will show the dates in one column
starting with start date and ending with end date using formatting
dddd dd-mmm-yy? I don't know how to do this, it would help me a great
deal if you could help out. It will be really appreciated!

Thanks,
Suzanne

 

[Kalle]

Not quite sure that I understood you right.

Try Edit/Fill/Row
(as I use german version, i guessed the english menu entries...)

 

[Bob Phillips]

Hi Suzanne,

Here is a shot

Sub InsertDates()
Dim i As Long

i = 2
With Worksheets("Sheet2")
.Cells(i, "A").Value = Worksheets("Parameter").Range("A3").Value
Do
i = i + 1
.Cells(i, "A").Value = .Cells(i - 1, "A").Value + 1
Loop Until .Cells(i, "A").Value =
Worksheets("Parameter").Range("A4").Value
.Columns(1).NumberFormat = "dddd dd-mmm-yyyy"
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Suzanne Windt]

I know there are easier ways to fill a column with data (eg fill
series), but I'm creating a file for someone who is not so familiar with
excel so I want to keep it simple stupid.

When the user fills in the start date and end date in the parameters
sheet, I would like to have a macro looking up the start date in the
parameters sheet, starting in sheet 2 at cell A2:

for example:
Friday 27/02/2004
Saturday 28/02/2004
Sunday 29/02/2004
etc..
until the end date.

I hope this is more clear?

Do you think a macro exists for this? I would think so, but couldn't
find it in previous posts.

Thanks again.

 

[Kalle]

Sub FillDates()
'adjust to your needs
Dim Parameters As Worksheet
Dim Sheet2 As Worksheet

'it is always better to use names from WorkSheets,
'so macros running correct even if user move the sheets arround
Set Parameters = Worksheets("Parameters")
Set Sheet2 = Worksheets("Sheet2")

i = 2

Do
'assuming startdate in cell A1
Parameters.Cells(i, 1).Value = Sheet2.Cells(1, 1).Value
'assuming enddate in cell B1
If Parameters.Cells(i, 1).Value = Sheet2.Cells(1, 2).Value Then
Exit Sub
End If
i = i + 1
Loop
End Sub

 

[Suzanne Windt]

I tried the macro and it doesn't work, it gives the following problem:
run time error 438 object doesn't support this property or method. It
debugs on this line:
Worksheets("Parameter").Range("A4").Value

Do you know what could be possibly wrong?

Suzanne

 

[Tom Ogilvy]

This should all be one command:

Loop Until .Cells(i, "A").Value =
Worksheets("Parameter").Range("A4").Value
.Columns(1).NumberFormat = "dddd dd-mmm-yyyy"


So you need to have line extension characters

Loop Until .Cells(i, "A").Value = _
Worksheets("Parameter").Range("A4").Value _
.Columns(1).NumberFormat = "dddd dd-mmm-yyyy"

 

[Bob Phillips]

Suzanne,

You are getting NG wrap-around. that and the previous line are all one line
of code. Try this version

Sub InsertDates()
Dim i As Long

i = 2
With Worksheets("Sheet2")
.Cells(i, "A").Value = Worksheets("Parameter").Range("A3").Value
Do
i = i + 1
.Cells(i, "A").Value = .Cells(i - 1, "A").Value + 1
Loop Until .Cells(i, "A").Value = _
Worksheets("Parameter").Range("A4").Value
.Columns(1).NumberFormat = "dddd dd-mmm-yyyy"
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Whoops, used too many line continuation characters. The third line should
not be part of the first two. So the correction is:

Loop Until .Cells(i, "A").Value = _
Worksheets("Parameter").Range("A4").Value

.Columns(1).NumberFormat = "dddd dd-mmm-yyyy"

Empty row added for emphasis.

 

[Suzanne Windt]

Wow, it works! Thanks

Having done this I realize that I would like to get rid of all the
Mondays in the list of dates. Is there an easy way to delete these lines
with a macro?

Suzanne

 

[Bob Phillips]

Suzanne,

You sound surprised!

Rather than delete them, it is easier not to create them in the first place

Sub InsertDatesWithoutMondays()
Dim i As Long

i = 2
With Worksheets("Sheet2")
.Cells(i, "A").Value = Worksheets("Parameter").Range("A3").Value
Do
i = i + 1
If Weekday(.Cells(i - 1, "A").Value + 1, vbSunday) = 2 Then
.Cells(i, "A").Value = .Cells(i - 1, "A").Value + 2
Else
.Cells(i, "A").Value = .Cells(i - 1, "A").Value + 1
End If
Loop Until .Cells(i, "A").Value = _
Worksheets("Parameter").Range("A4").Value
.Columns(1).NumberFormat = "dddd dd-mmm-yyyy"
End With

End Sub

If you must get rid of them then use

Sub RemoveMondays()
Dim i As Long

With Worksheets("Sheet2")
For i = .Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Weekday(.Cells(i, "A").Value, vbSunday) = 2 Then
.Cells(i, "A").EntireRow.Delete
End If
Next i
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)