Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Transpose 1 col into 3 cols
I am trying to transpose 1 column into 3 columns:
Example: 1 2 3 4 5 6 7 8 9 ect... into 123 456 789 ect... Here is the code I have: ----------------------- Dim i As Long i = 1 Do While i < 3001 Range("A1:A3").Select Application.CutCopyMode = False Selection.Copy Range(Cells(i, 2), Cells(i, 3)).Select problem here--Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks_:=False , Transpose:=True Range("A1").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp i = i + 1 Loop --------------------------------------- It keeps hanging up he Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks_:=False , Transpose:=True I have no clue as why it is hanging...any help? Thanks, James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Transpose 1 col into 3 cols
Range(Cells(i, 2), Cells(i, 3)).Select
should be Range(Cells(i, 2), Cells(i, 4)).Select or just use Cells(i,2) However, I would do something like Sub TransposeData() Dim i as Long, j as Long j = 1 for i = 1 to 3004 step 3 cells(i,1).Resize(3,1).copy cells(j,2).PasteSpecial xlAll, Transpose:=True j = j + 1 Next Columns(1).ClearContents end sub -- Regards, Tom Ogilvy " wrote: I am trying to transpose 1 column into 3 columns: Example: 1 2 3 4 5 6 7 8 9 ect... into 123 456 789 ect... Here is the code I have: ----------------------- Dim i As Long i = 1 Do While i < 3001 Range("A1:A3").Select Application.CutCopyMode = False Selection.Copy Range(Cells(i, 2), Cells(i, 3)).Select problem here--Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks_:=False , Transpose:=True Range("A1").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp i = i + 1 Loop --------------------------------------- It keeps hanging up he Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks_:=False , Transpose:=True I have no clue as why it is hanging...any help? Thanks, James |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Transpose 1 col into 3 cols
Thanks Tom!
Sweet Stuff! I have coded in Access vba before but some of the functions in excelI have no clue what they are refering to. Any recommendation of sites that would fill in the gaps? BTW the next beer is on me! ;) James Tom Ogilvy wrote: Range(Cells(i, 2), Cells(i, 3)).Select should be Range(Cells(i, 2), Cells(i, 4)).Select or just use Cells(i,2) However, I would do something like Sub TransposeData() Dim i as Long, j as Long j = 1 for i = 1 to 3004 step 3 cells(i,1).Resize(3,1).copy cells(j,2).PasteSpecial xlAll, Transpose:=True j = j + 1 Next Columns(1).ClearContents end sub -- Regards, Tom Ogilvy " wrote: I am trying to transpose 1 column into 3 columns: Example: 1 2 3 4 5 6 7 8 9 ect... into 123 456 789 ect... Here is the code I have: ----------------------- Dim i As Long i = 1 Do While i < 3001 Range("A1:A3").Select Application.CutCopyMode = False Selection.Copy Range(Cells(i, 2), Cells(i, 3)).Select problem here--Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks_:=False , Transpose:=True Range("A1").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp i = i + 1 Loop --------------------------------------- It keeps hanging up he Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks_:=False , Transpose:=True I have no clue as why it is hanging...any help? Thanks, James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Transpose 1 col into 3 cols
Chip Pearson
http://www.cpearson.com/excel.htm then go to the topics or pages index pages. That is a good place to start. John Walkenback (see developer tips) http://www.j-walk.com/ss/excel Debra Dalgleish (Pivot Tables, Data Validation, Conditional Formatting, Advanced and Autofilters, FAQ) http://www.contextures.com/tiptech.html Jon Peltier (charts) http://peltiertech.com/ Ron de Bruin http://www.rondebruin.nl/tips.htm David McRitchie http://www.mvps.org/dmcritchie/excel/excel.htm are some. Some of them have links. -- Regards, Tom Ogilvy " wrote: Thanks Tom! Sweet Stuff! I have coded in Access vba before but some of the functions in excelI have no clue what they are refering to. Any recommendation of sites that would fill in the gaps? BTW the next beer is on me! ;) James Tom Ogilvy wrote: Range(Cells(i, 2), Cells(i, 3)).Select should be Range(Cells(i, 2), Cells(i, 4)).Select or just use Cells(i,2) However, I would do something like Sub TransposeData() Dim i as Long, j as Long j = 1 for i = 1 to 3004 step 3 cells(i,1).Resize(3,1).copy cells(j,2).PasteSpecial xlAll, Transpose:=True j = j + 1 Next Columns(1).ClearContents end sub -- Regards, Tom Ogilvy " wrote: I am trying to transpose 1 column into 3 columns: Example: 1 2 3 4 5 6 7 8 9 ect... into 123 456 789 ect... Here is the code I have: ----------------------- Dim i As Long i = 1 Do While i < 3001 Range("A1:A3").Select Application.CutCopyMode = False Selection.Copy Range(Cells(i, 2), Cells(i, 3)).Select problem here--Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks_:=False , Transpose:=True Range("A1").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp i = i + 1 Loop --------------------------------------- It keeps hanging up he Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks_:=False , Transpose:=True I have no clue as why it is hanging...any help? Thanks, James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I transpose data from rows to cols with a formula not pivot ta | Excel Worksheet Functions | |||
2 Cols To 2 Cols VLookup Comparison | Excel Discussion (Misc queries) | |||
Range.Select 1st pass 13 cols, 2nd paqss 25 cols twice as wide in error? | Excel Programming | |||
Macro for single to multiple cols. Help! | New Users to Excel | |||
Cond Format:re color 2 cols, skip 2 cols | Excel Worksheet Functions |