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.

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

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

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

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