Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem
Can anyone debug this? Sub TransposeData3() sr = 1 dr = 0 For sr = 1 To 29 Step 10 ' Change 100 to suit number of rows in th source Column dr = dr + 1 Range(Cells(sr, 1), Cells(sr + 9, 1)).Select ' Select A1:A1 first loop, then A11:A20 next loop etc Selection.Copy Cells(dr, 2).Select ' Select B1 first loop, then B2 second loo etc Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Next End Su -- unknowndevic ----------------------------------------------------------------------- unknowndevice's Profile: http://www.excelforum.com/member.php...fo&userid=2664 View this thread: http://www.excelforum.com/showthread.php?threadid=39961 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem
Sub TransposeData3()
sr = 1 dr = 0 rw = Cells(Rows.Count, sr).End(xlUp) For sr = 1 To rw + 9 Step 10 dr = dr + 1 Range(Cells(sr, 1), Cells(sr + 9, 1)).Select Selection.Copy Cells(dr, 2).Select Selection.PasteSpecial _ Paste:=xlPasteAll, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=True Next End Sub -- Regards, Tom Ogilvy "unknowndevice" wrote in message news:unknowndevice.1ue3md_1125090326.0714@excelfor um-nospam.com... Can anyone debug this? Sub TransposeData3() sr = 1 dr = 0 For sr = 1 To 29 Step 10 ' Change 100 to suit number of rows in the source Column dr = dr + 1 Range(Cells(sr, 1), Cells(sr + 9, 1)).Select ' Select A1:A19 first loop, then A11:A20 next loop etc Selection.Copy Cells(dr, 2).Select ' Select B1 first loop, then B2 second loop etc Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Next End Sub -- unknowndevice ------------------------------------------------------------------------ unknowndevice's Profile: http://www.excelforum.com/member.php...o&userid=26646 View this thread: http://www.excelforum.com/showthread...hreadid=399610 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem
unknowndevice,
I think you may be expecting different results from the "step 10" portion of your for loop than what you'll actually get. E.g., Dim i As Long For i = 1 To 29 Step 10 Debug.Print i Next i results in 1, 11 and 21 whereas it looks like you're expecting 10, 20, 30 .... hth, Doug "unknowndevice" wrote in message news:unknowndevice.1ue3md_1125090326.0714@excelfor um-nospam.com... Can anyone debug this? Sub TransposeData3() sr = 1 dr = 0 For sr = 1 To 29 Step 10 ' Change 100 to suit number of rows in the source Column dr = dr + 1 Range(Cells(sr, 1), Cells(sr + 9, 1)).Select ' Select A1:A19 first loop, then A11:A20 next loop etc Selection.Copy Cells(dr, 2).Select ' Select B1 first loop, then B2 second loop etc Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Next End Sub -- unknowndevice ------------------------------------------------------------------------ unknowndevice's Profile: http://www.excelforum.com/member.php...o&userid=26646 View this thread: http://www.excelforum.com/showthread...hreadid=399610 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem
Think you are misreading the intent of the code. Printing out Sr and Area
to be transpose: 1 $A$1:$A$10 11 $A$11:$A$20 21 $A$21:$A$30 31 $A$31:$A$40 41 $A$41:$A$50 -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... unknowndevice, I think you may be expecting different results from the "step 10" portion of your for loop than what you'll actually get. E.g., Dim i As Long For i = 1 To 29 Step 10 Debug.Print i Next i results in 1, 11 and 21 whereas it looks like you're expecting 10, 20, 30 ... hth, Doug "unknowndevice" wrote in message news:unknowndevice.1ue3md_1125090326.0714@excelfor um-nospam.com... Can anyone debug this? Sub TransposeData3() sr = 1 dr = 0 For sr = 1 To 29 Step 10 ' Change 100 to suit number of rows in the source Column dr = dr + 1 Range(Cells(sr, 1), Cells(sr + 9, 1)).Select ' Select A1:A19 first loop, then A11:A20 next loop etc Selection.Copy Cells(dr, 2).Select ' Select B1 first loop, then B2 second loop etc Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Next End Sub -- unknowndevice ------------------------------------------------------------------------ unknowndevice's Profile: http://www.excelforum.com/member.php...o&userid=26646 View this thread: http://www.excelforum.com/showthread...hreadid=399610 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax problem
Tom,
I think you're right. Thanks, Doug "Tom Ogilvy" wrote in message ... Think you are misreading the intent of the code. Printing out Sr and Area to be transpose: 1 $A$1:$A$10 11 $A$11:$A$20 21 $A$21:$A$30 31 $A$31:$A$40 41 $A$41:$A$50 -- Regards, Tom Ogilvy "Doug Glancy" wrote in message ... unknowndevice, I think you may be expecting different results from the "step 10" portion of your for loop than what you'll actually get. E.g., Dim i As Long For i = 1 To 29 Step 10 Debug.Print i Next i results in 1, 11 and 21 whereas it looks like you're expecting 10, 20, 30 ... hth, Doug "unknowndevice" wrote in message news:unknowndevice.1ue3md_1125090326.0714@excelfor um-nospam.com... Can anyone debug this? Sub TransposeData3() sr = 1 dr = 0 For sr = 1 To 29 Step 10 ' Change 100 to suit number of rows in the source Column dr = dr + 1 Range(Cells(sr, 1), Cells(sr + 9, 1)).Select ' Select A1:A19 first loop, then A11:A20 next loop etc Selection.Copy Cells(dr, 2).Select ' Select B1 first loop, then B2 second loop etc Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Next End Sub -- unknowndevice ------------------------------------------------------------------------ unknowndevice's Profile: http://www.excelforum.com/member.php...o&userid=26646 View this thread: http://www.excelforum.com/showthread...hreadid=399610 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Syntax? | Excel Worksheet Functions | |||
Syntax problem | Excel Worksheet Functions | |||
Operator syntax - VBA problem | Excel Programming | |||
Another Syntax Problem | Excel Programming |