Hi armagan!

The line cell.Cut cell.Offset(0, 1) "cuts" the value and place it according
to "offset" parameter.

The offset parameter is a coordinate relative to the cell you cut. The first
number is the row and the second is the column. Numbers can range from
negative to positive.

The cell you "cut" has always the offset 0, 0 and if you would like to put
the "cut" value 3 rows below and 5 columns to the right the "offset" command
would be Offset(3, 5).

To put it 1 row up and 3 columns to the left you do this by Offset(-1, -3)

Offset will not work outside the rows and columns of the spreadsheet. If you
"cut" a value from say A1 you can not use a negative row "offset" since
there is no row above row 1. So always check using "offset" that you don't
try to place values outside the rows and columns in a spreadsheet.

Have modified the macro so it will insert a line and and place the "cut"
value on the row above and in column AD as you wrote. As fore the range it
will expand since you are putting a new line in it every time there is a
value in column AE so I think you better use Norman Jones range definition.
That is substitue in my example Range("AE2:AE15") with Range("AE:AE").

Don't forget that if you have a value in cell AE1 a row "offset" of -1 will
not work.



Sub Macro1()
'
Dim cell As Range

For Each cell In Range("AE2:AE15")
If cell.Value < "" Then
cell.EntireRow.Insert
cell.Cut cell.Offset(-1, -1)
End If
Next cell
'
End Sub





"armagan" wrote in
message ...

Alf,
I want to modify your formula so that when it searches this column for
values, when it finds a value. I want it to insert a new row just
above the value and cut and paste the 'found' value to a cell in that
new row (in column AD).

hope that makes sense.

Armagan


--
armagan
------------------------------------------------------------------------
armagan's Profile:
http://www.excelforum.com/member.php...o&userid=34237
View this thread: http://www.excelforum.com/showthread...hreadid=545448