Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
Hi,
I try to learn how to write macros€¦ having problem with arranging a cycle. I have a data that looks like that: Column A consist accounts #, rows B through M the $ value, meaningfully attached to the cells B2, C2€¦M2. A B C ... M 06/01/31 06/02/28 March - Nov 06/12/31 1111111 26000 28000 30000 22000 2222222 11000 10500 10000 12500 3333333 13000 11500 20000 21000 I need to rearrange my data to look like that: 1111111^06/01/31^26000 1111111^06/02/28^28000 €¦ 1111111^06/12/31^22000 The same has to be repeated 12 times for every next account until the end of the sheet. How would I do a macro for that? Any help will be appreciated, Thanks a lot, Lu. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
This will get you very close. It's John Walkenbach's "reverse pivottable"
instructions. http://j-walk.com/ss/excel/usertips/tip068.htm If you really need that format, you could use a helper column of cells: =A2&"^"&TEXT(B2,"mm/dd/yy")&"^"&C2 Then copy that column and paste special to a new worksheet (or just copy|paste into Notepad and save from there). Lu wrote: Hi, I try to learn how to write macros€¦ having problem with arranging a cycle. I have a data that looks like that: Column A consist accounts #, rows B through M the $ value, meaningfully attached to the cells B2, C2€¦M2. A B C ... M 06/01/31 06/02/28 March - Nov 06/12/31 1111111 26000 28000 30000 22000 2222222 11000 10500 10000 12500 3333333 13000 11500 20000 21000 I need to rearrange my data to look like that: 1111111^06/01/31^26000 1111111^06/02/28^28000 €¦ 1111111^06/12/31^22000 The same has to be repeated 12 times for every next account until the end of the sheet. How would I do a macro for that? Any help will be appreciated, Thanks a lot, Lu. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
Thanks, Dave.
I have tried the Pivot Table, but it didn't work the way I needed: 1. There were only 11 fields instead of 12 months I selected. 2. All the data was "mixed up" -- sorted as it would for a chart, in ascending order -- and I needed to keep the data "how it is", because it has meaningful attachment to the certain month in my spreadsheet. Any suggestions...? -- Thanks for your help, Lu. "Dave Peterson" wrote: This will get you very close. It's John Walkenbach's "reverse pivottable" instructions. http://j-walk.com/ss/excel/usertips/tip068.htm If you really need that format, you could use a helper column of cells: =A2&"^"&TEXT(B2,"mm/dd/yy")&"^"&C2 Then copy that column and paste special to a new worksheet (or just copy|paste into Notepad and save from there). Lu wrote: Hi, I try to learn how to write macros€¦ having problem with arranging a cycle. I have a data that looks like that: Column A consist accounts #, rows B through M the $ value, meaningfully attached to the cells B2, C2€¦M2. A B C ... M 06/01/31 06/02/28 March - Nov 06/12/31 1111111 26000 28000 30000 22000 2222222 11000 10500 10000 12500 3333333 13000 11500 20000 21000 I need to rearrange my data to look like that: 1111111^06/01/31^26000 1111111^06/02/28^28000 €¦ 1111111^06/12/31^22000 The same has to be repeated 12 times for every next account until the end of the sheet. How would I do a macro for that? Any help will be appreciated, Thanks a lot, Lu. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
Maybe...
Option Explicit Sub testme01() Dim curWks As Worksheet Dim newWks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Dim FirstCol As Long Dim LastCol As Long Dim iCol As Long Dim oRow As Long Set curWks = Worksheets("sheet1") Set newWks = Worksheets.Add With curWks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column oRow = 0 For iRow = FirstRow To LastRow For iCol = FirstCol To LastCol If IsEmpty(.Cells(iRow, iCol)) Then 'do nothing Else oRow = oRow + 1 newWks.Cells(oRow, "A").Value _ = .Cells(iRow, "A").Value & "^" _ & .Cells(1, iCol).Text & "^" _ & .Cells(iRow, iCol).Value End If Next iCol Next iRow End With End Sub The "if isempty()..." line will skip any blank cells within that table. If you don't want that, then remove the "If through Else" and associated "end if" lines. Lu wrote: Thanks, Dave. I have tried the Pivot Table, but it didn't work the way I needed: 1. There were only 11 fields instead of 12 months I selected. 2. All the data was "mixed up" -- sorted as it would for a chart, in ascending order -- and I needed to keep the data "how it is", because it has meaningful attachment to the certain month in my spreadsheet. Any suggestions...? -- Thanks for your help, Lu. "Dave Peterson" wrote: This will get you very close. It's John Walkenbach's "reverse pivottable" instructions. http://j-walk.com/ss/excel/usertips/tip068.htm If you really need that format, you could use a helper column of cells: =A2&"^"&TEXT(B2,"mm/dd/yy")&"^"&C2 Then copy that column and paste special to a new worksheet (or just copy|paste into Notepad and save from there). Lu wrote: Hi, I try to learn how to write macros€¦ having problem with arranging a cycle. I have a data that looks like that: Column A consist accounts #, rows B through M the $ value, meaningfully attached to the cells B2, C2€¦M2. A B C ... M 06/01/31 06/02/28 March - Nov 06/12/31 1111111 26000 28000 30000 22000 2222222 11000 10500 10000 12500 3333333 13000 11500 20000 21000 I need to rearrange my data to look like that: 1111111^06/01/31^26000 1111111^06/02/28^28000 €¦ 1111111^06/12/31^22000 The same has to be repeated 12 times for every next account until the end of the sheet. How would I do a macro for that? Any help will be appreciated, Thanks a lot, Lu. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros
If you've formatted the original cells the way you like, you could use:
..Cells(iRow, iCol).text 'instead of .value if you want to do your own formatting: format(.cells(irow,icol).value,"000.0000") (or whatever numberformat you want.) My source has been these newsgroups. But there are books that can help. Debra Dalgleish has a list at her site: http://www.contextures.com/xlbooks.html John Walkenbach's is a nice one to start with. See if you can find them in your local bookstore and you can choose what one you like best. Lu wrote: FANTASTIC, DAVE !!! :)) The macro you've sent does work. Thanks, thanks, thanks ! The last detail only: how to insert rounding into the & .Cells(iRow, iCol).Value as my new application wouldn't accept 11 digits after decimal that Excel throws in it. And another question: what was your source, I wish to learn to write macros as well as you do! -- Thanks for your help, Lu. "Dave Peterson" wrote: Maybe... Option Explicit Sub testme01() Dim curWks As Worksheet Dim newWks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Dim FirstCol As Long Dim LastCol As Long Dim iCol As Long Dim oRow As Long Set curWks = Worksheets("sheet1") Set newWks = Worksheets.Add With curWks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 2 LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column oRow = 0 For iRow = FirstRow To LastRow For iCol = FirstCol To LastCol If IsEmpty(.Cells(iRow, iCol)) Then 'do nothing Else oRow = oRow + 1 newWks.Cells(oRow, "A").Value _ = .Cells(iRow, "A").Value & "^" _ & .Cells(1, iCol).Text & "^" _ & .Cells(iRow, iCol).Value End If Next iCol Next iRow End With End Sub The "if isempty()..." line will skip any blank cells within that table. If you don't want that, then remove the "If through Else" and associated "end if" lines. Lu wrote: Thanks, Dave. I have tried the Pivot Table, but it didn't work the way I needed: 1. There were only 11 fields instead of 12 months I selected. 2. All the data was "mixed up" -- sorted as it would for a chart, in ascending order -- and I needed to keep the data "how it is", because it has meaningful attachment to the certain month in my spreadsheet. Any suggestions...? -- Thanks for your help, Lu. "Dave Peterson" wrote: This will get you very close. It's John Walkenbach's "reverse pivottable" instructions. http://j-walk.com/ss/excel/usertips/tip068.htm If you really need that format, you could use a helper column of cells: =A2&"^"&TEXT(B2,"mm/dd/yy")&"^"&C2 Then copy that column and paste special to a new worksheet (or just copy|paste into Notepad and save from there). Lu wrote: Hi, I try to learn how to write macros€¦ having problem with arranging a cycle. I have a data that looks like that: Column A consist accounts #, rows B through M the $ value, meaningfully attached to the cells B2, C2€¦M2. A B C ... M 06/01/31 06/02/28 March - Nov 06/12/31 1111111 26000 28000 30000 22000 2222222 11000 10500 10000 12500 3333333 13000 11500 20000 21000 I need to rearrange my data to look like that: 1111111^06/01/31^26000 1111111^06/02/28^28000 €¦ 1111111^06/12/31^22000 The same has to be repeated 12 times for every next account until the end of the sheet. How would I do a macro for that? Any help will be appreciated, Thanks a lot, Lu. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
Macros inside macros, and pasting into macro code. | Excel Programming | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
Open workbook-macros enabled, opening another with macros | Excel Programming | |||
Macro Size Limit / open macros with macros? | Excel Programming |