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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.