RE: Data Move

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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



.

Relevant Pages

  • Re: Error 2042
    ... Are you trying to find an exact match? ... > Dim sb As Variant ... Prev by Date: ...
    (microsoft.public.excel)
  • Re: Type mismatch with DLookup
    ... dim x as variant ... "Duane Hookom" wrote: ... Prev by Date: ...
    (microsoft.public.access.modulesdaovba)
  • Re: IF & OR Functions
    ... > Please keep all correspondence within the Group, ... >> I'm trying to check a cell on sheet 1 for certain text such as ... Prev by Date: ...
    (microsoft.public.excel.newusers)
  • Re: Getting the row/column number
    ... If row number and column number of selected cell ... dim x1,x2 as variant ... Prev by Date: ...
    (microsoft.public.excel.programming)
  • RE: CountIF?
    ... >> proper result for how many times one character shows up in one column (aka ... but am trying to get a result where everytime a C and W ... >> different cell? ... Prev by Date: ...
    (microsoft.public.excel.misc)