Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
The way I have a macro setup right now I can import multiple text files
into one worksheet. THe problem is it stacks it on top of one another going down two columns. I want to make a second macro that will take these two columns and break it into multiple columns so that each file has its own unique two columns to do analysis on. The code I tried writing was simple and was Range("A177:B352").Select Selection.Cut Range("C1").Select Selection.Paste Range("A353:B528").Select Selection.Cut Range("E1").Select Selection.Paste Range("A529:B704").Select Selection.Cut Range("G1").Select Selection.Paste Range("A705:B880").Select Selection.Cut Range("I1").Select and so on and so forth to move 14 different files from two columns to 28 columns. I get an error message that says Object doesn't support this property or method. Is there any easier way of doing this or am I missing something in my code. Thanks Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
Soe each text file is exactly 175 lines long?
If yes, you can try: Option Explicit Sub testme() Dim wks As Worksheet Dim oCol As Long Dim iRow As Long Dim myStep As Long Set wks = Worksheets("sheet1") myStep = 175 oCol = 1 With wks For iRow = 177 To .Cells(.Rows.Count, "A").End(xlUp).Row Step myStep oCol = oCol + 2 .Cells(iRow, "A").Resize(myStep, 2).Cut _ Destination:=.Cells(1, oCol) Next iRow End With End Sub yanks6rule wrote: The way I have a macro setup right now I can import multiple text files into one worksheet. THe problem is it stacks it on top of one another going down two columns. I want to make a second macro that will take these two columns and break it into multiple columns so that each file has its own unique two columns to do analysis on. The code I tried writing was simple and was Range("A177:B352").Select Selection.Cut Range("C1").Select Selection.Paste Range("A353:B528").Select Selection.Cut Range("E1").Select Selection.Paste Range("A529:B704").Select Selection.Cut Range("G1").Select Selection.Paste Range("A705:B880").Select Selection.Cut Range("I1").Select and so on and so forth to move 14 different files from two columns to 28 columns. I get an error message that says Object doesn't support this property or method. Is there any easier way of doing this or am I missing something in my code. Thanks Bob -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
Hi Bob,
judging by the numbers in your code, each file is 176 lines long (352 -177 + 1 = 176), so in Dave's code just change "myStep = 175" "myStep = 176" and you should get the correct result if all files have the same number of lines. Ken Johnson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
That should have read "myStep = 175" to "myStep = 176".
Ken Johnson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
Darn arithmetic!
Thanks for the correction. Ken Johnson wrote: That should have read "myStep = 175" to "myStep = 176". Ken Johnson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
You're welcome Dave.
Ken Johnson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
So do I just change the myStep to 176 or do I have to put in "myStep =
175" to "myStep = 176" |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
So do I just change the myStep to 176 or do I have to put in "myStep =
175" to "myStep = 176". Thanks for all the help guys I am going to copy this and try it now |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
Just change this one line:
from: myStep = 175 to yanks6rule wrote: So do I just change the myStep to 176 or do I have to put in "myStep = 175" to "myStep = 176". Thanks for all the help guys I am going to copy this and try it now -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
Just change this one line
from: myStep = 175 to: myStep = 176 yanks6rule wrote: So do I just change the myStep to 176 or do I have to put in "myStep = 175" to "myStep = 176". Thanks for all the help guys I am going to copy this and try it now -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
Dave,
This works great except for one flaw, it cuts and pastes the second dataset correctly but it puts it in C2 instead of C1 but then in the third dataset it goes back to E1 but it start with the last data point of the previous set so I get point 176 at the top instead of point one. Does this make sense and if so what does that mean? Thanks for all your help Bob |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving Data
It worked....thanks everyone for their help, this will shave hours off
of my data analysis. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
moving data in excel without deleting existing data | Excel Discussion (Misc queries) | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |