Making a planning sheet

[Gabrie]

Hi

I have the following table:

House Start End
08A 1-01-07 05-01-07
37E 1-01-07 05-01-07
04F 1-01-07 05-01-07
81E 1-01-07 05-01-07
32A 1-01-07 05-01-07
07A 1-01-07 05-01-07
29A 1-01-07 05-01-07
27C 1-01-07 05-01-07
15C 1-01-07 05-01-07
06F 3-01-07 07-01-07
32A 5-01-07 08-01-07
78C 5-01-07 13-01-07
32A 8-01-07 15-01-07
01D 12-01-07 15-01-07
01D 19-01-07 22-01-07
57B 19-01-07 22-01-07
35C 26-01-07 29-01-07
65A 26-01-07 29-01-07
03F 26-01-07 29-01-07

These are houses I lent and the begin and end date of the periode
somebody has rented the house. Now I would like to create a planning
sheet. For each month I would like a quick overview of which houses
are free and which are occupied.

Something like this
date -> Jan 1 2 3 4 5 6 7
08A - X X X X - -
09A - - - - X X X
10A - - - - X X X

etc .etc

I've been working on something, but this requires me to create so many
help-cells and if then calculations, that it slows down the sheet
enormously and it is not easy to work with.

Would writing a program in VBA the easiest way to go? Does someone
maybe have some examples of this?

With kind regards
Gabrie

 

[Greg Glynn]

Hi

I have the following table:

House Start End
08A 1-01-07 05-01-07
37E 1-01-07 05-01-07
04F 1-01-07 05-01-07
81E 1-01-07 05-01-07
32A 1-01-07 05-01-07
07A 1-01-07 05-01-07
29A 1-01-07 05-01-07
27C 1-01-07 05-01-07
15C 1-01-07 05-01-07
06F 3-01-07 07-01-07
32A 5-01-07 08-01-07
78C 5-01-07 13-01-07
32A 8-01-07 15-01-07
01D 12-01-07 15-01-07
01D 19-01-07 22-01-07
57B 19-01-07 22-01-07
35C 26-01-07 29-01-07
65A 26-01-07 29-01-07
03F 26-01-07 29-01-07

These are houses I lent and the begin and end date of the periode
somebody has rented the house. Now I would like to create a planning
sheet. For each month I would like a quick overview of which houses
are free and which are occupied.

Something like this
date -> Jan 1 2 3 4 5 6 7
08A - X X X X - -
09A - - - - X X X
10A - - - - X X X

etc .etc

I've been working on something, but this requires me to create so many
help-cells and if then calculations, that it slows down the sheet
enormously and it is not easy to work with.

Would writing a program in VBA the easiest way to go? Does someone
maybe have some examples of this?

With kind regards
Gabrie

Gabrie:

Try this:

Press F11 and copy this macro into the VBA editor

Sub Matrix()
For Each c In Sheets("Sheet1").Range("Houses")
For Each d In Sheets("Sheet1").Range("Dates")
If c.Offset(0, 1) <= d Then
If c.Offset(0, 2) >= d Then
d.Offset(c.Row - 2).Value = "X"
End If
End If
Next d
Next c
End Sub

Set up a sheet "Sheet1" like this:

A3:A20 is filled with your House Names. Give it a Range Name of
"Houses"
B3:B20 is filled with your START DATES
C3:C20 is filled with your FINISH DATES
D2:AH2 is the dates 01/01/07 .... 31/01/07. Give it a Range Name of
DATES.

Run the Macro

 

[Greg Glynn]

Here is version 2, improved to clear the matrix cells if the result is
NOT in the range (handy for changing dates)

Sub Matrix()
For Each c In Sheets("Sheet1").Range("Houses")
For Each d In Sheets("Sheet1").Range("Dates")
If c.Offset(0, 1) <= d Then
If c.Offset(0, 2) >= d Then
d.Offset(c.Row - 2).Value = "X"
End If
Else
d.Offset(c.Row - 2).Clear
End If
Next d
Next c
End Sub