Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than one cell answers??????
I have a worksheet
in colum A:A I have dates then in colum C:C and colum D:D I have numbers. Using a formula how I do I find and move, or copy, ((ALL)) the 2/1/07-2/28/07 records into the E:E, F:F, and G:G colums and so on and so forth down to december? for example: a c d 2/1/07 100 234 3/1/07 100 456 2/1/07 50 545 a c d e f g 3/1/07 100 456 2/1/07 100 234 2/1/07 50 545 -- Thank you. Hope we can work this out. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than one cell answers??????
Here is a start
' some code needed here to determine the number of rows. ' Assumed 31 in this case. Sort by date Range("A1:C31").Sort Key1:=Range("A29"), Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ' some code needed here to find which rows are the same month ' then move those rows to the correct columns Range("A5:C7").Cut Destination:=Range("D7:F7") ' make sure the column width is wide enough Columns("D:D").EntireColumn.AutoFit "mr2u53" wrote: I have a worksheet in colum A:A I have dates then in colum C:C and colum D:D I have numbers. Using a formula how I do I find and move, or copy, ((ALL)) the 2/1/07-2/28/07 records into the E:E, F:F, and G:G colums and so on and so forth down to december? for example: a c d 2/1/07 100 234 3/1/07 100 456 2/1/07 50 545 a c d e f g 3/1/07 100 456 2/1/07 100 234 2/1/07 50 545 -- Thank you. Hope we can work this out. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than one cell answers??????
thank you for your reply. number of rows is A:A the whole sheet.
the order of the original must stay the same. So I should use copy instead of cut right? so know that I have the allingment copyed i just do simple math right? -- Thank you. Hope we can work this out. "Gleam" wrote: Here is a start ' some code needed here to determine the number of rows. ' Assumed 31 in this case. Sort by date Range("A1:C31").Sort Key1:=Range("A29"), Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ' some code needed here to find which rows are the same month ' then move those rows to the correct columns Range("A5:C7").Cut Destination:=Range("D7:F7") ' make sure the column width is wide enough Columns("D:D").EntireColumn.AutoFit "mr2u53" wrote: I have a worksheet in colum A:A I have dates then in colum C:C and colum D:D I have numbers. Using a formula how I do I find and move, or copy, ((ALL)) the 2/1/07-2/28/07 records into the E:E, F:F, and G:G colums and so on and so forth down to december? for example: a c d 2/1/07 100 234 3/1/07 100 456 2/1/07 50 545 a c d e f g 3/1/07 100 456 2/1/07 100 234 2/1/07 50 545 -- Thank you. Hope we can work this out. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than one cell answers??????
In your spec the rows do not stay in the same order.
Here is some code which will do as I think you want (assuming your data starts in A29): Sub MoveData() StartRow = 29 Nrows = Cells(StartRow, 1).CurrentRegion.Rows.Count For i1 = StartRow To StartRow + Nrows - 1 n1 = Month(Cells(i1, 1)) If n1 1 Then ' dont move January newcol = 2 + 3 * (n1 - 1) ' leave one blank column after January ' so if macro rerun current region not affected Cells(i1, 1).Resize(1, 3).Cut Destination:=Cells(i1, newcol) Columns(newcol).EntireColumn.AutoFit End If Next i1 End Sub "mr2u53" wrote: thank you for your reply. number of rows is A:A the whole sheet. the order of the original must stay the same. So I should use copy instead of cut right? so know that I have the allingment copyed i just do simple math right? -- Thank you. Hope we can work this out. "Gleam" wrote: Here is a start ' some code needed here to determine the number of rows. ' Assumed 31 in this case. Sort by date Range("A1:C31").Sort Key1:=Range("A29"), Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ' some code needed here to find which rows are the same month ' then move those rows to the correct columns Range("A5:C7").Cut Destination:=Range("D7:F7") ' make sure the column width is wide enough Columns("D:D").EntireColumn.AutoFit "mr2u53" wrote: I have a worksheet in colum A:A I have dates then in colum C:C and colum D:D I have numbers. Using a formula how I do I find and move, or copy, ((ALL)) the 2/1/07-2/28/07 records into the E:E, F:F, and G:G colums and so on and so forth down to december? for example: a c d 2/1/07 100 234 3/1/07 100 456 2/1/07 50 545 a c d e f g 3/1/07 100 456 2/1/07 100 234 2/1/07 50 545 -- Thank you. Hope we can work this out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FAQ : Answers to show in another cell | Excel Worksheet Functions | |||
Formula to calculate 4 possible answers in one cell | Excel Worksheet Functions | |||
only want certain answers allowed to be entered in a cell | Excel Discussion (Misc queries) | |||
I need to return one of three answers in a cell | Excel Worksheet Functions | |||
How do I have a list of possible answers auto put into a cell? | Excel Worksheet Functions |