Copying cells within a loop
 

I have two columns of data, column B a current observed price and colum
A a last observed price. At the end of a macro I want to copy th
current observed price to last observed price and clear the current.
The problem is that there are many blanks in the current observe
price, and I don't want a price in column A to be overwritten with
blank cell.
I'm trying to build a loop on column B where if cell value < "" the
copy the cell, but can't get the code right. Can anyone help? Currentl
I have-

Range("v65536").End(xlUp).Select
Range("v5", ActiveCell).Select
For Each cell In Selection
If cell.Value < "" Then
ActiveCell.Copy
Paste.cell.Offset(-1, 0)
End If
Next cell

The problem is the ActiveCell.Copy and the paste code- what should i
be?
Any help greatly appreciated!
Pau

--
Message posted from http://www.ExcelForum.com

Copying cells within a loop
 

PaulSin,
Try

For Each Cell In _
Range("b5", Range("b65536").End(xlUp))
If Cell.Value < "" Then
Cell.Offset(0, -1).Value = Cell.Value
Cell.ClearContents
End If
Next Cell

Cecil
But why delete old price you may need it in future for evaluations, better
to store it some where else for records before deletion.

"PaulSin " wrote in message
...
I have two columns of data, column B a current observed price and column
A a last observed price. At the end of a macro I want to copy the
current observed price to last observed price and clear the current.
The problem is that there are many blanks in the current observed
price, and I don't want a price in column A to be overwritten with a
blank cell.
I'm trying to build a loop on column B where if cell value < "" then
copy the cell, but can't get the code right. Can anyone help? Currently
I have-

Range("v65536").End(xlUp).Select
Range("v5", ActiveCell).Select
For Each cell In Selection
If cell.Value < "" Then
ActiveCell.Copy
Paste.cell.Offset(-1, 0)
End If
Next cell

The problem is the ActiveCell.Copy and the paste code- what should it
be?
Any help greatly appreciated!
Paul


---
Message posted from http://www.ExcelForum.com/

Copying cells within a loop
 

Paul,

Not sure whether this is a typo in your code or whether
you were just trying to simplify the post for the NG, but
this will look at every value in column B and paste into
column A if not blank. Just adjust the references in the
RANGE and CELLS statement, if it's column V you're really
checking:

LastRow = Range("B65536").end(xlUp).Row

For X = 5 to LastRow

If Cells(X,2) <"" Then
Cells(X,1).value=Cells(X,2).value
Cells(X,1).clearcontents
End IF

Next X

-----Original Message-----
I have two columns of data, column B a current observed
price and column
A a last observed price. At the end of a macro I want to
copy the
current observed price to last observed price and clear
the current.
The problem is that there are many blanks in the current
observed
price, and I don't want a price in column A to be
overwritten with a
blank cell.
I'm trying to build a loop on column B where if cell
value < "" then
copy the cell, but can't get the code right. Can anyone
help? Currently
I have-

Range("v65536").End(xlUp).Select
Range("v5", ActiveCell).Select
For Each cell In Selection
If cell.Value < "" Then
ActiveCell.Copy
Paste.cell.Offset(-1, 0)
End If
Next cell

The problem is the ActiveCell.Copy and the paste code-
what should it
be?
Any help greatly appreciated!
Paul


---
Message posted from http://www.ExcelForum.com/

.

Copying cells within a loop
 

AARGH! Just noticed a typo in my code. It should be:

Cells (x,2).clearcontents

Pete
-----Original Message-----
Paul,

Not sure whether this is a typo in your code or whether
you were just trying to simplify the post for the NG, but
this will look at every value in column B and paste into
column A if not blank. Just adjust the references in the
RANGE and CELLS statement, if it's column V you're really
checking:

LastRow = Range("B65536").end(xlUp).Row

For X = 5 to LastRow

If Cells(X,2) <"" Then
Cells(X,1).value=Cells(X,2).value
Cells(X,1).clearcontents
End IF

Next X

-----Original Message-----
I have two columns of data, column B a current observed
price and column
A a last observed price. At the end of a macro I want to
copy the
current observed price to last observed price and clear
the current.
The problem is that there are many blanks in the current
observed
price, and I don't want a price in column A to be
overwritten with a
blank cell.
I'm trying to build a loop on column B where if cell
value < "" then
copy the cell, but can't get the code right. Can anyone
help? Currently
I have-

Range("v65536").End(xlUp).Select
Range("v5", ActiveCell).Select
For Each cell In Selection
If cell.Value < "" Then
ActiveCell.Copy
Paste.cell.Offset(-1, 0)
End If
Next cell

The problem is the ActiveCell.Copy and the paste code-
what should it
be?
Any help greatly appreciated!
Paul


---
Message posted from http://www.ExcelForum.com/

.

.