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!
|