highlight range, apply calculation to data in cells and paste special to same range

  • Thread starter Thread starter S Himmelrich
  • Start date Start date
S

S Himmelrich

I know how to select a range of cells and copy:

Range("O2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

but now
1. applying the calculation, which should be value of cell * 1000
2. special past values only to same selected sells

?
 
You enter 1000 in a separate blank cell and copy it then highlight the range
to be multiplied and past special-> multiply. that will multiply all the
cells by 1000 in the pasted range.

Regards,

OssieMac
 
Himmelrich,

If you're going to do this with a macro, you can do it like this. You don't need the
copy/paste, or the selection part.

Dim Thing As Range
For Each Thing In Range("O2", Range("O2").SpecialCells(xlLastCell))
Thing.Value = Thing.Value * 1000
Next Thing

I used your SpecialCells(xlLastCell) construct, but note that that will include the cells
from O2 to the lower-right most cell in the sheet with anything in it (or ever had anything
in it). Maybe you meant only to go downwards in column O until there's an empty cell. In
that case, change the line to:

For Each Thing In Range(("O2"), Range("O2").End(xlDown))

Or if there might be empty cells in the column:

For Each Thing In Range("O2", Cells(Cells.Rows.Count, Range("O2").Column).End(xlUp))

These will fail if there is text in a cell being multiplied.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Back
Top