Hi,

I'm trying to put together some VBA where it will search a specified
column (column AE) for values. blank cells will be ignored, cells with
a value will be cut and pasted to a new location (column AF).

can someone give me some suggestion with the code?

cheers,


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

Hi Armagan,

Try something like:

'=============
Public Sub Tester()
Dim rng As Range

On Error Resume Next
Set rng = Range("AE:AE").SpecialCells(xlCellTypeConstants)
On Error Resume Next

If Not rng Is Nothing Then
rng.Copy Destination:=Range("AF1")
End If

End Sub
'<<=============


---
Regards,
Norman



"armagan" wrote in
message ...

Hi,

I'm trying to put together some VBA where it will search a specified
column (column AE) for values. blank cells will be ignored, cells with
a value will be cut and pasted to a new location (column AF).

can someone give me some suggestion with the code?

cheers,


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

Or somthing like this:

Sub Macro1()
'
Dim cell As Range

For Each cell In Range("AE1:AE20")
If cell.Value < "" Then
cell.Cut cell.Offset(0, 1)
End If
Next cell
'
End Sub


"Norman Jones" wrote in message
...
Hi Armagan,

Try something like:

'=============
Public Sub Tester()
Dim rng As Range

On Error Resume Next
Set rng = Range("AE:AE").SpecialCells(xlCellTypeConstants)
On Error Resume Next

If Not rng Is Nothing Then
rng.Copy Destination:=Range("AF1")
End If

End Sub
'<<=============


---
Regards,
Norman



"armagan" wrote in
message ...

Hi,

I'm trying to put together some VBA where it will search a specified
column (column AE) for values. blank cells will be ignored, cells with
a value will be cut and pasted to a new location (column AF).

can someone give me some suggestion with the code?

cheers,


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

Hi Armagan,

The suggested code copies rather than cuts. Therefore, instead try :

'=============
Public Sub Tester001A()
Dim rng As Range

On Error Resume Next
Set rng = Range("AE:AE").SpecialCells(xlCellTypeConstants)
On Error Resume Next

If Not rng Is Nothing Then
With rng
.Copy Destination:=Range("AF1")
.ClearContents
End With
End If

End Sub
'<<=============


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Armagan,

Try something like:

'=============
Public Sub Tester()
Dim rng As Range

On Error Resume Next
Set rng = Range("AE:AE").SpecialCells(xlCellTypeConstants)
On Error Resume Next

If Not rng Is Nothing Then
rng.Copy Destination:=Range("AF1")
End If

End Sub
'<<=============


---
Regards,
Norman

both ideas are good,

but I've just realised I want to insert a row above when I find a valu
in the column (and copy to the row above). Although generally the sam
formulas will work, it throws off the formula, as once the row i
inserted it changes the selected cell.

how would you modify the existing formula to account for this?

Thanks for your help!

Armaga

--
armaga
-----------------------------------------------------------------------
armagan's Profile: http://www.excelforum.com/member.php...fo&userid=3423
View this thread: http://www.excelforum.com/showthread.php?threadid=54544

Not sure I understand what you want to do but if you chage the line

cell.Cut cell.Offset(0, 1) to cell.Cut cell.Offset(-1, 1) a value found
in AE4 will be placed in AF3.

Be aware that that if you have data in cell AE1 this will not work.



"armagan" wrote in
message ...

both ideas are good,

but I've just realised I want to insert a row above when I find a value
in the column (and copy to the row above). Although generally the same
formulas will work, it throws off the formula, as once the row is
inserted it changes the selected cell.

how would you modify the existing formula to account for this?

Thanks for your help!

Armagan


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

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

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