Making a planning sheet
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 |
Making a planning sheet
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 |
Making a planning sheet
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 |
All times are GMT +1. The time now is 07:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com