I need to move cell data down 2 cells and over 1 cell to the left, then find
the next cell with data within the same column and repeat the move. Example:
B15 has a name in it and I need to move it to A17, then look 5 cells down in
column B for the next name and move it down 2 and over 1 cell. The number of
records varies greatly, so the macro needs to loop thru the records until
there are no more records.

The data is imported into Excel from a proprietary business application.

Thanks for any help!!

Sub Adjust Data()
set rng = Columns(2).SpecialCells(xlConstants)
for each cell in rng
cell.offset(2,-1).Value = cell
cell.ClearContents
Next
End sub

--
Regards,
Tom Ogilvy

"laralea" wrote in message
...
I need to move cell data down 2 cells and over 1 cell to the left, then
find
the next cell with data within the same column and repeat the move.
Example:
B15 has a name in it and I need to move it to A17, then look 5 cells down
in
column B for the next name and move it down 2 and over 1 cell. The number
of
records varies greatly, so the macro needs to loop thru the records until
there are no more records.

The data is imported into Excel from a proprietary business application.

Thanks for any help!!

This helps, but it also moves data from 2 cells below the name to the right
column.

"Tom Ogilvy" wrote:

Sub Adjust Data()
set rng = Columns(2).SpecialCells(xlConstants)
for each cell in rng
cell.offset(2,-1).Value = cell
cell.ClearContents
Next
End sub

--
Regards,
Tom Ogilvy

"laralea" wrote in message
...
I need to move cell data down 2 cells and over 1 cell to the left, then
find
the next cell with data within the same column and repeat the move.
Example:
B15 has a name in it and I need to move it to A17, then look 5 cells down
in
column B for the next name and move it down 2 and over 1 cell. The number
of
records varies greatly, so the macro needs to loop thru the records until
there are no more records.

The data is imported into Excel from a proprietary business application.

Thanks for any help!!

Here is a modification to only work on cells that are not empty in
columns(2) [my omission, sorry]

Sub AdjustData()
Set rng = Columns(2).SpecialCells(xlConstants)
For Each cell In rng
If Not IsEmpty(cell) Then
cell.Offset(2, -1).Value = cell
cell.ClearContents
' Debug.Print cell.Address, cell.Offset(2, -1).Address
End If
Next
End Sub

Then testing it out

$B$15 $A$17
$B$20 $A$22


seemed to work as you described you wanted.

--
regards,
Tom Ogilvy

"laralea" wrote in message
...
This helps, but it also moves data from 2 cells below the name to the
right
column.

"Tom Ogilvy" wrote:

Sub Adjust Data()
set rng = Columns(2).SpecialCells(xlConstants)
for each cell in rng
cell.offset(2,-1).Value = cell
cell.ClearContents
Next
End sub

--
Regards,
Tom Ogilvy

"laralea" wrote in message
...
I need to move cell data down 2 cells and over 1 cell to the left,
then
find
the next cell with data within the same column and repeat the move.
Example:
B15 has a name in it and I need to move it to A17, then look 5 cells
down
in
column B for the next name and move it down 2 and over 1 cell. The
number
of
records varies greatly, so the macro needs to loop thru the records
until
there are no more records.

The data is imported into Excel from a proprietary business
application.

Thanks for any help!!