Trailing zero's
Donald Waddell
with trailing zero's assumed, ie. 35.0 means 35,000. I
need to change all of these from assumed 1,000's to
numbers with all trailing zeros. Is there a quick way to
do this short of doing each cell one by one?
Bob Phillips
This macro does it. Select all applicable cells and run it
Sub MultiplyByAThousand()
For Each cell in Selection
cell.Value = cell.Value * 1000
Next cell
End Sub
--
HTH
-------
Bob Phillips
... looking out across Poole Harbour to the Purbecks
"Donald Waddell" <donald....@gm.com> wrote in message
news:044c01c31f91$55c293c0$a001...@phx.gbl...
nike
write 1000 into one cell,copy it,
select the cells you want to edit
right click them and do Special Paste with
the option multiply selected...
All cells will be multiplied by that factor.
Another option would be to change the
System Setting to german, as we use the .
to split thousands ;-)
Bye
Nike
>.
>
David McRitchie
[show entire number instead of thousands representation]
See Nike's solution for something that works without a macro.
The macro as shown is only good if every cell in the range has a
number -- it would be very unwise to select an entire column.
The problem with this macro is that you are multiplying everything
by 1000 whether there is a number or not and so the macro
will terminate at some point, some cells will probably have been
multiplied by 1000 before the termination and cells after the point
of failure will not be processed..
You can bypass that problem by inserting On Error Resume
but you will be converting empty cells to zeros, which is not what
you want.
If you use SpecialCells then you solve two problems, the
empty cells will not be included and the cells out of the used
range will not be included. So you can select entire columns
without messing up and without taking several minutes. You
will have a problem with formulas. The simplest approach is to only
process the cells with number constants. See my proper.htm
page for more information on Special Cells.
You should dimension your variables: i.e. cell is a range
To help remind you to dim your variables use Option Explicit
Since the macro is now written properly there is no need for
On Error Resume Next so it is commented out.
Option Explicit
Sub MultiplyByAThousand()
Dim cell As Range
'-- On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlNumbers))
cell.Value = cell.Value * 1000
Next cell
End Sub
As I indicated the above is ignoring formulas. You could treat them
as values and wipe out the formulas, which is probably not good.
If you have a lot of these to do from time to time the advantage is
certainly with the macro if it does what you want.
With Nike's solution you don't have to worry about whether you have
text or empty cells (both ignored), number constants (multiplied),
or formulas (redone with parens if needed and multiplied). Since
this is strictly Excel and no VBA logic it runs very fast.
There is a method one could use to utilize Bob's original macro
but it requires extra work and if you forget and chose an entire
column you could take several minutes (depending on your processor
speed) or I guess hours if you selected the entire sheet. That method
would be to make the selection contain only number constants before
running the macro.
- make selection i.e. a couple of columns
- Edit, GoTo (Ctrl+G), Special (button), Constants and Numbers
do not include Text etc., and don't be confused that Numbers
looks like it is under only Formulas.
- With your selection now reduced to number constants, you can
run the original macro without problems.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Bob Phillips" <bob.ph...@tiscali.co.uk> wrote ...
> This macro does it. Select all applicable cells and run it
>
> Sub MultiplyByAThousand()
> For Each cell in Selection
> cell.Value = cell.Value * 1000
> Next cell
> End Sub
>
Bob Phillips
think he is thinking of implementing an application built around this.
Bob
"David McRitchie" <dmcri...@msn.com> wrote in message
news:uyblkA6H...@tk2msftngp13.phx.gbl...
David McRitchie
I don't think anyone including Donald wants their
spreadsheet messed up with a macro that can do one of several
things: mess up data, eliminate formulas, terminate abnormally,
introduce zeros, take several minutes to run. But it does illustrate
a number of things to watch out for in macros.
Clearly the worksheet solution is better than a macro that is run without
fully understanding the consequences of what is selected before
running the macro. Like everything the poster has to make their
own decision from what is offered, no guarantees, no refunds.
Or better put, test on a copy of data, and have backup.
. . http://www.mvps.org/dmcritchie/excel/backup.htm
Building an application, hmmm, certainly could have turned off
calculation and screen updating, but that might not be necessary
if Alan Beban can rewrite the macro to run without a loop.
Got the 1000 into the clipboard with help from Chip Pearson's
clipboar.htm webpage, but wasn't good enough PasteSpecial
is looking for a single cell range to start with.
Dim MyDataObj As New DataObject
MyDataObj.SetText 1000
MyDataObj.PutInClipboard
Selection.PasteSpecial Paste:=xlAll, Operation:=xlMultiply, _
SkipBlanks:= True, Transpose:=False
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Bob Phillips" <bob.ph...@tiscali.co.uk> wrote in message news:uiOyeo7H...@TK2MSFTNGP10.phx.gbl...