| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Relative Cell position NOT working with or without macro
I am trying to move 4 cells within one column to 4 cells within one row, ie:
A2, A3, A4, A5 to B1, C1, D1, E1 by using a macro. I want to repeat the macro then on cells A8, A9, A10, A11 moving them to rows A7, B7, C7, D7, etc. See the pattern? IE I am converting data in a column to data in rows. The first 3 different files I tried this on, relative cell position worked great. Then all of a sudden on the next file (and every file since) the macro always runs as if it is using absolute cell references. No matter what cell on the sheet I start the macro from, it always go back and tries the use A2,3,4,5 and move it to B1, C1, D1, E1. It worked in the beginning but now it doesn't. Can anyone Help???? Thanks Sub Macro1() ' ' Macro1 Macro ' Macro recorded 5/30/05 by new ' ' Keyboard Shortcut: Ctrl+m ' Selection.Cut Range("B1").Select ActiveSheet.Paste Range("A3").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Range("A4").Select Selection.Cut Range("D1").Select ActiveSheet.Paste Range("A5").Select Selection.Cut Range("E1").Select ActiveSheet.Paste Range("A8").Select End Sub |
|
#2
|
|||
|
|||
|
I am not real sure of the pattern you are trying to develop
A2:A5 to B1:E1 is not really the same pattern as A8:A11 to A7:D7 The macro you have is definately absolutes only. If what you want to do is A2:A5 to B1:E1 A8:A11 to B7:E7 and Next series A14:A17to B13:E13 Then in fyour macro something like for rr = 1 to 61 step 6 for cc = 1 to 4 cells(rr,1+cc)=cells(rr+cc,1) next cc next rr You will have ot play with it if you want a different pattern. "Scratching my Head" wrote: I am trying to move 4 cells within one column to 4 cells within one row, ie: A2, A3, A4, A5 to B1, C1, D1, E1 by using a macro. I want to repeat the macro then on cells A8, A9, A10, A11 moving them to rows A7, B7, C7, D7, etc. See the pattern? IE I am converting data in a column to data in rows. The first 3 different files I tried this on, relative cell position worked great. Then all of a sudden on the next file (and every file since) the macro always runs as if it is using absolute cell references. No matter what cell on the sheet I start the macro from, it always go back and tries the use A2,3,4,5 and move it to B1, C1, D1, E1. It worked in the beginning but now it doesn't. Can anyone Help???? Thanks Sub Macro1() ' ' Macro1 Macro ' Macro recorded 5/30/05 by new ' ' Keyboard Shortcut: Ctrl+m ' Selection.Cut Range("B1").Select ActiveSheet.Paste Range("A3").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Range("A4").Select Selection.Cut Range("D1").Select ActiveSheet.Paste Range("A5").Select Selection.Cut Range("E1").Select ActiveSheet.Paste Range("A8").Select End Sub |
|
#4
|
|||
|
|||
|
I guess I did a pretty bad job of describing my question. I truly appreciate
your reply but it didn't do what I needed. Let me ask it this way, I am pasting a copy of a simple macro below. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 5/30/05 by new ' ' Keyboard Shortcut: Ctrl+z ' Range("G2:G4").Select Selection.Copy Range("H1:J1").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Range("G7").Select End Sub The problem I have is that is copies and pastes the same data over and over no matter what cell I have selected when I run the macro. What I need is for the macro to run from the cell I have selected at the time I run the macro and to perform the copy and paste operations relative to the cell that is selected when the macro is ran. Can you help? Thanks Still sctatching my head. "Harald Staff" wrote: Hi If you read your code, it's pretty obvious what to expect from it: Whatever selected is cut. B1 is selected and Paste. A3 is selected and Cut C1 is selected and Paste .... and so on. Your pattern isn't too obvious, but this should work with the data described: Sub Macro1() Dim R As Long, C As Long For R = ActiveCell.Row To ActiveCell.Row + 12 Step 6 For C = 1 To 4 Cells(R + C, 1).Cut _ Destination:=Cells(R, C + 1) Next Next End Sub HTH. Best wishes Harald "Scratching my Head" <Scratching my skrev i melding ... I am trying to move 4 cells within one column to 4 cells within one row, ie: A2, A3, A4, A5 to B1, C1, D1, E1 by using a macro. I want to repeat the macro then on cells A8, A9, A10, A11 moving them to rows A7, B7, C7, D7, etc. See the pattern? IE I am converting data in a column to data in rows. The first 3 different files I tried this on, relative cell position worked great. Then all of a sudden on the next file (and every file since) the macro always runs as if it is using absolute cell references. No matter what cell on the sheet I start the macro from, it always go back and tries the use A2,3,4,5 and move it to B1, C1, D1, E1. It worked in the beginning but now it doesn't. Can anyone Help???? Thanks Sub Macro1() ' ' Macro1 Macro ' Macro recorded 5/30/05 by new ' ' Keyboard Shortcut: Ctrl+m ' Selection.Cut Range("B1").Select ActiveSheet.Paste Range("A3").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Range("A4").Select Selection.Cut Range("D1").Select ActiveSheet.Paste Range("A5").Select Selection.Cut Range("E1").Select ActiveSheet.Paste Range("A8").Select End Sub |
|
#5
|
|||
|
|||
|
Sure
Sub Macro1() Dim R As Long, C As Long, L As Long R = ActiveCell.Row C = ActiveCell.Column For L = 1 To 4 Cells(R + L - 1, C).Cut _ Destination:=Cells(R - 1, C + L) Next End Sub But how will the code know that the first time 4 cells was to be transposed, now you want only 3 ? HTH. Best wishes Harald "scratching my head" <scratching my skrev i melding ... I guess I did a pretty bad job of describing my question. I truly appreciate your reply but it didn't do what I needed. Let me ask it this way, I am pasting a copy of a simple macro below. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 5/30/05 by new ' ' Keyboard Shortcut: Ctrl+z ' Range("G2:G4").Select Selection.Copy Range("H1:J1").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Range("G7").Select End Sub The problem I have is that is copies and pastes the same data over and over no matter what cell I have selected when I run the macro. What I need is for the macro to run from the cell I have selected at the time I run the macro and to perform the copy and paste operations relative to the cell that is selected when the macro is ran. Can you help? Thanks Still sctatching my head. "Harald Staff" wrote: Hi If you read your code, it's pretty obvious what to expect from it: Whatever selected is cut. B1 is selected and Paste. A3 is selected and Cut C1 is selected and Paste .... and so on. Your pattern isn't too obvious, but this should work with the data described: Sub Macro1() Dim R As Long, C As Long For R = ActiveCell.Row To ActiveCell.Row + 12 Step 6 For C = 1 To 4 Cells(R + C, 1).Cut _ Destination:=Cells(R, C + 1) Next Next End Sub HTH. Best wishes Harald "Scratching my Head" <Scratching my skrev i melding ... I am trying to move 4 cells within one column to 4 cells within one row, ie: A2, A3, A4, A5 to B1, C1, D1, E1 by using a macro. I want to repeat the macro then on cells A8, A9, A10, A11 moving them to rows A7, B7, C7, D7, etc. See the pattern? IE I am converting data in a column to data in rows. The first 3 different files I tried this on, relative cell position worked great. Then all of a sudden on the next file (and every file since) the macro always runs as if it is using absolute cell references. No matter what cell on the sheet I start the macro from, it always go back and tries the use A2,3,4,5 and move it to B1, C1, D1, E1. It worked in the beginning but now it doesn't. Can anyone Help???? Thanks Sub Macro1() ' ' Macro1 Macro ' Macro recorded 5/30/05 by new ' ' Keyboard Shortcut: Ctrl+m ' Selection.Cut Range("B1").Select ActiveSheet.Paste Range("A3").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Range("A4").Select Selection.Cut Range("D1").Select ActiveSheet.Paste Range("A5").Select Selection.Cut Range("E1").Select ActiveSheet.Paste Range("A8").Select End Sub |
|
#6
|
|||
|
|||
|
Great Harald, I'm almost there. How can I make the last thing the macro does
is select the cell in the same beginning column but exactly 5 rows down? ie: the ending selected cell is the original active cell number plus 5. Thanks, Danny (not scratching as much now) "Harald Staff" wrote: Sure Sub Macro1() Dim R As Long, C As Long, L As Long R = ActiveCell.Row C = ActiveCell.Column For L = 1 To 4 Cells(R + L - 1, C).Cut _ Destination:=Cells(R - 1, C + L) Next End Sub But how will the code know that the first time 4 cells was to be transposed, now you want only 3 ? HTH. Best wishes Harald "scratching my head" <scratching my skrev i melding ... I guess I did a pretty bad job of describing my question. I truly appreciate your reply but it didn't do what I needed. Let me ask it this way, I am pasting a copy of a simple macro below. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 5/30/05 by new ' ' Keyboard Shortcut: Ctrl+z ' Range("G2:G4").Select Selection.Copy Range("H1:J1").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Range("G7").Select End Sub The problem I have is that is copies and pastes the same data over and over no matter what cell I have selected when I run the macro. What I need is for the macro to run from the cell I have selected at the time I run the macro and to perform the copy and paste operations relative to the cell that is selected when the macro is ran. Can you help? Thanks Still sctatching my head. "Harald Staff" wrote: Hi If you read your code, it's pretty obvious what to expect from it: Whatever selected is cut. B1 is selected and Paste. A3 is selected and Cut C1 is selected and Paste .... and so on. Your pattern isn't too obvious, but this should work with the data described: Sub Macro1() Dim R As Long, C As Long For R = ActiveCell.Row To ActiveCell.Row + 12 Step 6 For C = 1 To 4 Cells(R + C, 1).Cut _ Destination:=Cells(R, C + 1) Next Next End Sub HTH. Best wishes Harald "Scratching my Head" <Scratching my skrev i melding ... I am trying to move 4 cells within one column to 4 cells within one row, ie: A2, A3, A4, A5 to B1, C1, D1, E1 by using a macro. I want to repeat the macro then on cells A8, A9, A10, A11 moving them to rows A7, B7, C7, D7, etc. See the pattern? IE I am converting data in a column to data in rows. The first 3 different files I tried this on, relative cell position worked great. Then all of a sudden on the next file (and every file since) the macro always runs as if it is using absolute cell references. No matter what cell on the sheet I start the macro from, it always go back and tries the use A2,3,4,5 and move it to B1, C1, D1, E1. It worked in the beginning but now it doesn't. Can anyone Help???? Thanks Sub Macro1() ' ' Macro1 Macro ' Macro recorded 5/30/05 by new ' ' Keyboard Shortcut: Ctrl+m ' Selection.Cut Range("B1").Select ActiveSheet.Paste Range("A3").Select Selection.Cut Range("C1").Select ActiveSheet.Paste Range("A4").Select Selection.Cut Range("D1").Select ActiveSheet.Paste Range("A5").Select Selection.Cut Range("E1").Select ActiveSheet.Paste Range("A8").Select End Sub |
|
#7
|
|||
|
|||
|
Good.
Activecell.Offset(5, 0).Select HTH. Best wishes Harald "scratching my head" skrev i melding ... Great Harald, I'm almost there. How can I make the last thing the macro does is select the cell in the same beginning column but exactly 5 rows down? ie: the ending selected cell is the original active cell number plus 5. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
| How do I sum using relative cell positions? | Excel Worksheet Functions | |||
| Relative Cell References within VBA code | Excel Discussion (Misc queries) | |||
| Relative Macro Help on Keystrokes | Excel Discussion (Misc queries) | |||
Linear Mode
