Format a Range to Multiply by 1000
 

I am trying to make a worksheet where any numerical value I type is
automatically multiplied by 1000.

I am working with lots of numbers but I do not want to type all the zeros.
For example, I want to be able to type 749.1 and have 749100 appear in the
cell. I know how to do this for just one cell but I cannot figure out how to do
this for a range of cells. Specifically, I want this to be applied to all the cells in
columns F-H.

Any suggestions?
Thanks

Format a Range to Multiply by 1000
 

ToolsOptionsEdit.

Checkmark Fixed Decimal Places and set to -3(minus 3)

Note: this is a global setting and may not be suitable.

In that case you may want to use worksheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value * 1000
End With
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

Alt + q to return to Excel.

Start entering numbers.


Gord



On Thu, 16 Feb 2012 22:51:41 +0000, REReed
wrote:


I am trying to make a worksheet where any numerical value I type is
automatically multiplied by 1000.

I am working with lots of numbers but I do not want to type all the
zeros.
For example, I want to be able to type 749.1 and have 749100 appear in
the
cell. I know how to do this for just one cell but I cannot figure out
how to do
this for a range of cells. Specifically, I want this to be applied to
all the cells in
columns F-H.

Any suggestions?
Thanks

Format a Range to Multiply by 1000
 

Another way is to use Paste Special...
Enter the numbers without the 3 trailing zeros.
Enter 1000 in a separate cell, away from the data, and copy it.
Select your data and then choose Paste Special from the Edit menu and then select Multiply.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Formats & Styles xl add-in: lists/removes unused styles & number formats) - free




"REReed"
wrote in message
...

I am trying to make a worksheet where any numerical value I type is
automatically multiplied by 1000.

I am working with lots of numbers but I do not want to type all the
zeros.
For example, I want to be able to type 749.1 and have 749100 appear in
the
cell. I know how to do this for just one cell but I cannot figure out
how to do
this for a range of cells. Specifically, I want this to be applied to
all the cells in
columns F-H.

Any suggestions?
Thanks




--
REReed

This macro works perfect, thanks.

Is there anyway to have a blank cell appear after
a value has been deleted rather than a zero?

Format a Range to Multiply by 1000
 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Not IsNumeric(Target.Value) _
Or Target.Value = "" Then Exit Sub
Application.EnableEvents = False
With Target
..Value = .Value * 1000
End With
Application.EnableEvents = True
End Sub


Gord


On Fri, 17 Feb 2012 20:59:29 +0000, REReed
wrote:


This macro works perfect, thanks.

Is there anyway to have a blank cell appear after
a value has been deleted rather than a zero?


'Gord Dibben[_2_ Wrote:
;1410297']ToolsOptionsEdit.

Checkmark Fixed Decimal Places and set to -3(minus 3)

Note: this is a global setting and may not be suitable.

In that case you may want to use worksheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value * 1000
End With
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

Alt + q to return to Excel.

Start entering numbers.


Gord



On Thu, 16 Feb 2012 22:51:41 +0000, REReed
wrote:
-

I am trying to make a worksheet where any numerical value I type is
automatically multiplied by 1000.

I am working with lots of numbers but I do not want to type all the
zeros.
For example, I want to be able to type 749.1 and have 749100 appear in
the
cell. I know how to do this for just one cell but I cannot figure out
how to do
this for a range of cells. Specifically, I want this to be applied to
all the cells in
columns F-H.

Any suggestions?
Thanks-

Format a Range to Multiply by 1000
 

IsNumeric is not a good test of a number.

i.e. '123 is seen as a number along with 123

Please change

If Not IsNumeric(Target.Value) _
Or Target.Value = "" Then Exit Sub

to

If Not Application.IsNumber(Target.Value) _
Or Target.Value = "" Then Exit Sub


Thanks, Gord

On Fri, 17 Feb 2012 19:15:43 -0800, Gord Dibben
wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub

Application.EnableEvents = False
With Target
.Value = .Value * 1000
End With
Application.EnableEvents = True
End Sub


Gord


On Fri, 17 Feb 2012 20:59:29 +0000, REReed
wrote:


This macro works perfect, thanks.

Is there anyway to have a blank cell appear after
a value has been deleted rather than a zero?


'Gord Dibben[_2_ Wrote:
;1410297']ToolsOptionsEdit.

Checkmark Fixed Decimal Places and set to -3(minus 3)

Note: this is a global setting and may not be suitable.

In that case you may want to use worksheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value * 1000
End With
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

Alt + q to return to Excel.

Start entering numbers.


Gord



On Thu, 16 Feb 2012 22:51:41 +0000, REReed
wrote:
-

I am trying to make a worksheet where any numerical value I type is
automatically multiplied by 1000.

I am working with lots of numbers but I do not want to type all the
zeros.
For example, I want to be able to type 749.1 and have 749100 appear in
the
cell. I know how to do this for just one cell but I cannot figure out
how to do
this for a range of cells. Specifically, I want this to be applied to
all the cells in
columns F-H.

Any suggestions?
Thanks-