Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Move
Here is what the data sets look like:
_______________________________ Comp Name : Joe Schmoe Inc Prior Year Charge Sales: Parts YTD : 0.00 Parts Prev: 7,669.92 Serv YTD : 0.00 Serv Prev : 1,827.95 _______________________________ Cust Name : Schmoe, Joe Prior Year Charge Sales: Parts YTD : 0.00 Parts Prev: 104.00 Serv YTD : 0.00 Serv Prev : 0.00 It continues like this for about 100 pages. Now these are All one column, that is how it is imported, so example: The row that says Parts Prev: 7,669.92 is one cell. My goal is to do the following: Take ALL cells that contain the Parts Prev: in them and move them up three rows to an empty cell two columns to the right of the Company Name or Customer name. First I have had to do a data text to columns to separate out Parts Prev: and the numbers into two columns. Then: Here is the formula I have been playing around with, but it is not doing what I want: Dim x As Variant Dim y As Variant For Each c In Range("A1:A20") If c.Value = "Parts Prev: 7,669.92" Then x = c.Cells.Row y = c.Cells.Column Cells(x, y + 4) = Cells(x + 3, y) End If Next End Sub This selects the cell 3 rows above the Parts Prev: then copies it and moves it 4 columns to the right of where the Parts Prev: was and pastes it there. I of course want the Parts Prev: to be selected and moved up next to the name. ULTIMATELY I want the Parts Prev: as well as the varying numbers that are next to it to both move up there. Make sense??? Any ideas what I'm doing wrong, or better ways of doing this? Thanks for the Help!!!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Move
Hi
I would start from a different place! I suggest that you read the original text file in with VBA and write the data to a spreadsheet in an orderly manner. This will be easier and faster in the end -- Tony Green "bodhisatvaofboogie" wrote: Here is what the data sets look like: _______________________________ Comp Name : Joe Schmoe Inc Prior Year Charge Sales: Parts YTD : 0.00 Parts Prev: 7,669.92 Serv YTD : 0.00 Serv Prev : 1,827.95 _______________________________ Cust Name : Schmoe, Joe Prior Year Charge Sales: Parts YTD : 0.00 Parts Prev: 104.00 Serv YTD : 0.00 Serv Prev : 0.00 It continues like this for about 100 pages. Now these are All one column, that is how it is imported, so example: The row that says Parts Prev: 7,669.92 is one cell. My goal is to do the following: Take ALL cells that contain the Parts Prev: in them and move them up three rows to an empty cell two columns to the right of the Company Name or Customer name. First I have had to do a data text to columns to separate out Parts Prev: and the numbers into two columns. Then: Here is the formula I have been playing around with, but it is not doing what I want: Dim x As Variant Dim y As Variant For Each c In Range("A1:A20") If c.Value = "Parts Prev: 7,669.92" Then x = c.Cells.Row y = c.Cells.Column Cells(x, y + 4) = Cells(x + 3, y) End If Next End Sub This selects the cell 3 rows above the Parts Prev: then copies it and moves it 4 columns to the right of where the Parts Prev: was and pastes it there. I of course want the Parts Prev: to be selected and moved up next to the name. ULTIMATELY I want the Parts Prev: as well as the varying numbers that are next to it to both move up there. Make sense??? Any ideas what I'm doing wrong, or better ways of doing this? Thanks for the Help!!!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move data | Excel Worksheet Functions | |||
Move data? | Setting up and Configuration of Excel | |||
Macro to move data to different column based on data in another co | Excel Discussion (Misc queries) | |||
enter data in cell which will start macro to move data to sheet2 | Excel Discussion (Misc queries) | |||
create macro to move label type data to column data | Excel Programming |