Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move data to column based on startdate
Hi,
I'm using Excel 2007 and I'm trying to figure out how to write a macro that will copy input data from one table (which is not date dependent) to a new table with dates in the first row. I would like the data to be copied starting at the column which matches the startdate. I have figured out a complicated way to do this using the OFFSET function in the spreadsheet but its a mess. I'd prefer to do it in VBA to keep it a little tidier. I also want to avoid absolute cell references. Here's an example: Input Table Project Startdate $Period1 $Period2 $Period3 ProjectA 2010 $1 $2 $3 ProjectB 2012 $10 $12 $14 ProjectC 2011 $3 $4 $5 I need a macro that will transform the above table into the following: Project 2010 2011 2012 2013 2014 Project A $1 $2 $3 $0 $0 Project B $0 $0 $10 $12 $14 Project C $0 $3 $4 $5 $0 Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move data to column based on startdate
Public Sub ProcessData()
Dim sh As Worksheet Dim LastRow As Long Dim NumYears As Long Dim i As Long Dim j As Long Set sh = Worksheets("Sheet2") With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row sh.Range("A1").Value = "Project" NumYears = Application.Max(.Columns(2)) + 3 - Application.Min(.Columns(2)) For i = 2 To NumYears + 1 sh.Cells(1, i).Value = Application.Min(.Columns(2)) + i - 2 Next i For i = 2 To LastRow sh.Cells(i, "A").Value = .Cells(i, "A").Value sh.Cells(i, "B").Resize(, NumYears).Value = 0 For j = 3 To 5 sh.Cells(i, Application.Match(.Cells(i, "B").Value, sh.Rows(1), 0) + j - 3).Value = .Cells(i, j).Value Next j Next i End With End Sub -- __________________________________ HTH Bob "J" wrote in message ... Hi, I'm using Excel 2007 and I'm trying to figure out how to write a macro that will copy input data from one table (which is not date dependent) to a new table with dates in the first row. I would like the data to be copied starting at the column which matches the startdate. I have figured out a complicated way to do this using the OFFSET function in the spreadsheet but its a mess. I'd prefer to do it in VBA to keep it a little tidier. I also want to avoid absolute cell references. Here's an example: Input Table Project Startdate $Period1 $Period2 $Period3 ProjectA 2010 $1 $2 $3 ProjectB 2012 $10 $12 $14 ProjectC 2011 $3 $4 $5 I need a macro that will transform the above table into the following: Project 2010 2011 2012 2013 2014 Project A $1 $2 $3 $0 $0 Project B $0 $0 $10 $12 $14 Project C $0 $3 $4 $5 $0 Any help would be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
move column data based on value of another column | Excel Programming | |||
move toi next column based on outside factors | Excel Discussion (Misc queries) | |||
move contents of column C based on criteria related to column A | Excel Discussion (Misc queries) | |||
Macro to move data to different column based on data in another co | Excel Discussion (Misc queries) | |||
How do I move data based on a conditional column search? | Excel Programming |