Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions,microsoft.public.excel.charting,microsoft.public.word.vba.general
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions,microsoft.public.excel.charting,microsoft.public.word.vba.general
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming,microsoft.public.word.vba.general
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Error in Visual Basic | Excel Discussion (Misc queries) | |||
microsoft visual basic compile error can't find library | Setting up and Configuration of Excel | |||
Visual Basic I/O error when opening Excel file | Excel Worksheet Functions | |||
Trust access to visual basic project in XP systems | Excel Discussion (Misc queries) | |||
Relative Cell position NOT working with or without macro | Excel Discussion (Misc queries) |