Visual Basic Macros, relative position
 

Dear Experts,

I am making a report, and I have a set of data, in a very looong row:

MKT_VAL NET_ASSETS TOT_ASSETS
46 51 51
6233 6228 6228


Over 100 fields.

I need this data to be changed to run down a column.


MKT_VAL
46
6233


NET_ASSETS
51
6228


TOT_ASSETS
51
6228


I tried to make a macro to do this.
It would be run after I copied and pasted the three column cells into
another area.


Sub ShiftNullData()
'
' ShiftNullData Macro
' Macro recorded 11/14/2006 by Rodger Lepinsky
'
' Keyboard Shortcut: Ctrl+q
'
Range("A18").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Cut
Range("B17").Select
ActiveSheet.Paste
Range("A19").Select
Selection.Cut
Range("C17").Select
ActiveSheet.Paste
Range("B17").Select
End Sub


However, it is working on the hard coded position. A18, B17, etc.

How can I get the macro to work from whereever it starts?


Alternatively, is there a way to pivot all this, with no kinds of
summaries, etc?



Thanks!

Visual Basic Macros, relative position
 

Public Sub test()
Dim iLastCol As Long
Dim i As Long, j As Long

With ActiveSheet

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
j = 1
For i = 2 To iLastCol
j = j + 3
.Cells(1, i).Resize(3).Copy .Cells(j, "A")
Next i
.Cells(1, "B").Resize(3, iLastCol).ClearContents

End With

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

wrote in message
oups.com...
Dear Experts,

I am making a report, and I have a set of data, in a very looong row:

MKT_VAL NET_ASSETS TOT_ASSETS
46 51 51
6233 6228 6228


Over 100 fields.

I need this data to be changed to run down a column.


MKT_VAL
46
6233


NET_ASSETS
51
6228


TOT_ASSETS
51
6228


I tried to make a macro to do this.
It would be run after I copied and pasted the three column cells into
another area.


Sub ShiftNullData()
'
' ShiftNullData Macro
' Macro recorded 11/14/2006 by Rodger Lepinsky
'
' Keyboard Shortcut: Ctrl+q
'
Range("A18").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Cut
Range("B17").Select
ActiveSheet.Paste
Range("A19").Select
Selection.Cut
Range("C17").Select
ActiveSheet.Paste
Range("B17").Select
End Sub


However, it is working on the hard coded position. A18, B17, etc.

How can I get the macro to work from whereever it starts?


Alternatively, is there a way to pivot all this, with no kinds of
summaries, etc?



Thanks!

Visual Basic Macros, relative position
 

Rodger

If you name your very long row and the two rows of data under it "test"
then run the following code it should put the existing data in columns
with the active cell at the top left.

Sub test()

For i = 1 To Range("test").Rows.Count
For j = 1 To Range("test").Columns.Count
ActiveCell.Offset(j - 1, i - 1).Value = Range("test").Cells(i,
j).Value
Next j
Next i

End Sub

Good luck.

Ken
Norfolk, Va





wrote:
Dear Experts,

I am making a report, and I have a set of data, in a very looong row:

MKT_VAL NET_ASSETS TOT_ASSETS
46 51 51
6233 6228 6228


Over 100 fields.

I need this data to be changed to run down a column.


MKT_VAL
46
6233


NET_ASSETS
51
6228


TOT_ASSETS
51
6228


I tried to make a macro to do this.
It would be run after I copied and pasted the three column cells into
another area.


Sub ShiftNullData()
'
' ShiftNullData Macro
' Macro recorded 11/14/2006 by Rodger Lepinsky
'
' Keyboard Shortcut: Ctrl+q
'
Range("A18").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Cut
Range("B17").Select
ActiveSheet.Paste
Range("A19").Select
Selection.Cut
Range("C17").Select
ActiveSheet.Paste
Range("B17").Select
End Sub


However, it is working on the hard coded position. A18, B17, etc.

How can I get the macro to work from whereever it starts?


Alternatively, is there a way to pivot all this, with no kinds of
summaries, etc?



Thanks!

Visual Basic Macros, relative position
 

Hi,
Might try a copy and paste special where you can change rows to columns.
--
David


" wrote:

Dear Experts,

I am making a report, and I have a set of data, in a very looong row:

MKT_VAL NET_ASSETS TOT_ASSETS
46 51 51
6233 6228 6228


Over 100 fields.

I need this data to be changed to run down a column.


MKT_VAL
46
6233


NET_ASSETS
51
6228


TOT_ASSETS
51
6228


I tried to make a macro to do this.
It would be run after I copied and pasted the three column cells into
another area.


Sub ShiftNullData()
'
' ShiftNullData Macro
' Macro recorded 11/14/2006 by Rodger Lepinsky
'
' Keyboard Shortcut: Ctrl+q
'
Range("A18").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Cut
Range("B17").Select
ActiveSheet.Paste
Range("A19").Select
Selection.Cut
Range("C17").Select
ActiveSheet.Paste
Range("B17").Select
End Sub


However, it is working on the hard coded position. A18, B17, etc.

How can I get the macro to work from whereever it starts?


Alternatively, is there a way to pivot all this, with no kinds of
summaries, etc?



Thanks!