PDA

View Full Version : Solved: producing multiple records from a list



Beatrix
03-26-2012, 10:24 AM
Hi Everyone ,

I need to create a small database by producing multiple records from a simple list in excel2010. Each record in summary list should run into database tab by calculating daily rns figures for each month between feb12-jan13. It should also repeat the same figure per calendar day. I attached a sample spreadsheet to be clear on that. I was wondering do you know the VBA script to run this data scenario?

Cheers
Yeliz

Bob Phillips
03-26-2012, 10:57 AM
Do you work for Starwood?


Sub CreateDatabase()
Dim sh As Worksheet
Dim lastrow As Long
Dim lastcol As Long
Dim nextrow As Long
Dim startDate As Date
Dim endDate As Date
Dim numDays As Long
Dim i As Long, j As Long

Application.ScreenUpdating = False

Set sh = Worksheets("database")
With sh

.Range("A1:E1").Value = Array("date", "country", "agent", "room type", "rns figures per day")

nextrow = 2
End With

With Worksheets("summary")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 2 To lastrow

For j = 4 To lastcol

startDate = DateValue("01-" & .Cells(1, j).Value & "-2012")
endDate = startDate + 32 - Day(startDate + 32)
numDays = endDate - startDate + 1
sh.Cells(nextrow, "A").Value = startDate
sh.Cells(nextrow + 1, "A").Value = startDate + 1
sh.Cells(nextrow, "A").Resize(2).AutoFill sh.Cells(nextrow, "A").Resize(numDays)
sh.Cells(nextrow, "B").Resize(numDays).Value = .Cells(i, "A").Value
sh.Cells(nextrow, "C").Resize(numDays).Value = .Cells(i, "B").Value
sh.Cells(nextrow, "D").Resize(numDays).Value = .Cells(i, "C").Value
sh.Cells(nextrow, "E").Resize(numDays).Value = .Cells(i, j).Value / numDays
sh.Cells(nextrow, "E").Resize(numDays).NumberFormat = "0.0"
nextrow = nextrow + numDays
Next j
Next i
End With

sh.Columns("A:E").AutoFit

Application.ScreenUpdating = True
End Sub

Beatrix
03-27-2012, 02:03 AM
This is sooo cool:beerchug: Thanks very much. I appreciate for it!

Nope, I don't work for Starwood:cloud9:

Cheers
Yeliz



Do you work for Starwood?


Sub CreateDatabase()
Dim sh As Worksheet
Dim lastrow As Long
Dim lastcol As Long
Dim nextrow As Long
Dim startDate As Date
Dim endDate As Date
Dim numDays As Long
Dim i As Long, j As Long

Application.ScreenUpdating = False

Set sh = Worksheets("database")
With sh

.Range("A1:E1").Value = Array("date", "country", "agent", "room type", "rns figures per day")

nextrow = 2
End With

With Worksheets("summary")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 2 To lastrow

For j = 4 To lastcol

startDate = DateValue("01-" & .Cells(1, j).Value & "-2012")
endDate = startDate + 32 - Day(startDate + 32)
numDays = endDate - startDate + 1
sh.Cells(nextrow, "A").Value = startDate
sh.Cells(nextrow + 1, "A").Value = startDate + 1
sh.Cells(nextrow, "A").Resize(2).AutoFill sh.Cells(nextrow, "A").Resize(numDays)
sh.Cells(nextrow, "B").Resize(numDays).Value = .Cells(i, "A").Value
sh.Cells(nextrow, "C").Resize(numDays).Value = .Cells(i, "B").Value
sh.Cells(nextrow, "D").Resize(numDays).Value = .Cells(i, "C").Value
sh.Cells(nextrow, "E").Resize(numDays).Value = .Cells(i, j).Value / numDays
sh.Cells(nextrow, "E").Resize(numDays).NumberFormat = "0.0"
nextrow = nextrow + numDays
Next j
Next i
End With

sh.Columns("A:E").AutoFit

Application.ScreenUpdating = True
End Sub

Bob Phillips
03-27-2012, 02:34 AM
Nope, I don't work for Starwood:cloud9:

I did some work for them recently, and the room codes looked very familiar. Perhaps it is industry wide.

Beatrix
03-27-2012, 04:03 AM
perhaps:yes


I did some work for them recently, and the room codes looked very familiar. Perhaps it is industry wide.