Automatically divide by 1000 all numbers entered in a range?

Fabulist

Board Regular
Joined
Jan 28, 2014
Messages
107
Hello everyone,

Would it be possible to automatically divide by 1000 all numbers entered in a range, for example all numbers entered from cell A1 to G25 will be transformed to =Number/1000?

Thank you in advance.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Use Paste special for this...
In a blank cell somewhere on the page enter 1000...
Now copy that cell
Select the range A1:G25 and right click
Select Paste special
in the popup menu select the "Divide" radio Button and click OK....all should now be divided
 
Upvote 0
Thank you, I am already aware of that solution, but I would like to know if it could be done automatically.
 
Upvote 0
By VBA...
First calculates any selected range
2nd one uses a fixed range

Code:
Sub MM1()
For Each cell In Selection
cell.Value = cell.Value / 1000
Next cell
End Sub


Code:
Sub MM2()
For Each cell In Range("A1:G25")
cell.Value = cell.Value / 1000
Next cell
End Sub
 
Last edited:
Upvote 0
Another way :
Code:
Sub v()
[A1:G25] = [A1:G25/1000]
End Sub

Or, provided that the selection is a contiguous range :
Code:
Sub vv()
Selection = Evaluate(Selection.Address & "/1000")
End Sub

Or, if the selection may sometimes be non-contiguous :
Code:
Sub vvv()
Dim area As Range
For Each area In Selection.Areas
    area = Evaluate(area.Address & "/1000")
Next
End Sub


Or perhaps you mean that if you enter 500 (say) anywhere within A1:G25, it automatically changes to 0.5 :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, area As Range
Set rng = Intersect(Target, [A5:G25])
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each area In Target
        area = Evaluate(area.Address & "/1000")
    Next
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Thank you both for your macros, the last one works for me footoo as I imagined it would. However, would it be possible that the numbers actually appear as they were typed and then have them divided?

For example: if I type 500, I get 0.5 and the cell shows 0.5.
I would like to type 500, get 0.5 and the cell to show 500/1000.

Is the second alternative possible?
 
Upvote 0
Also, whenever the tables are cleaned up via VBA or manually (del), the macro will start to automatically add 0s on the entire area that was erased, is this avoidable? Thanks again.
 
Upvote 0
Thank you both for your macros, the last one works for me footoo as I imagined it would. However, would it be possible that the numbers actually appear as they were typed and then have them divided?

For example: if I type 500, I get 0.5 and the cell shows 0.5.
I would like to type 500, get 0.5 and the cell to show 500/1000.

Is the second alternative possible?

I don't know how to do exactly what you want but if you format the cells : #.000"/1000"
The display will be :
.500/1000
1.600/1000
etc.
 
Upvote 0
Also, whenever the tables are cleaned up via VBA or manually (del), the macro will start to automatically add 0s on the entire area that was erased, is this avoidable? Thanks again.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [A1:G25])
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each cel In rng
        cel = cel / 1000
        If cel = 0 Then cel.ClearContents
    Next
    Application.EnableEvents = True
End If
End Sub
 
Last edited:
Upvote 0
Thank you, zeroes can be removed accordingly now.

I don't know how to do exactly what you want but if you format the cells : #.000"/1000"
The display will be :
.500/1000
1.600/1000
etc.

Well your macro divides everything typed in by 1000 and pastes the result. If you type 500 it will return 0.5 and if you highlight the cell it will report 0.5.

Is it possible that the cells report =500/1000 instead of 0.5?

In any case, thanks for everything, your vba is extremely helpful.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top