Copying cells within a loop

P

PaulSin

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
 
C

Cecilkumara Fernando

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.
 
P

Pete McCosh

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
 
P

Pete McCosh

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

Cells (x,2).clearcontents

Pete
 
Top