Format a Range to Multiply by 1000

[REReed]

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 th
zeros.
For example, I want to be able to type 749.1 and have 749100 appear i
the
cell. I know how to do this for just one cell but I cannot figure ou
how to do
this for a range of cells. Specifically, I want this to be applied t
all the cells in
columns F-H.

Any suggestions?
Thank

 

[Gord Dibben]

Tools>Options>Edit.

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

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[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?

;1410297']Tools>Options>Edit.

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

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

 

[Gord Dibben]

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

This macro works perfect, thanks.

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

;1410297']Tools>Options>Edit.

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

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-

 

[Gord Dibben]

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

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

This macro works perfect, thanks.

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

;1410297']Tools>Options>Edit.

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
-

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-