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

On Feb 15, 9:20 am, "Gabrie" wrote:
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

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