Data Move

[Guest]

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!!!!!!

 

[Guest]

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