Sub MM1()
For Each cell In Selection
cell.Value = cell.Value / 1000
Next cell
End Sub
Sub MM2()
For Each cell In Range("A1:G25")
cell.Value = cell.Value / 1000
Next cell
End Sub
Sub v()
[A1:G25] = [A1:G25/1000]
End Sub
Sub vv()
Selection = Evaluate(Selection.Address & "/1000")
End Sub
Sub vvv()
Dim area As Range
For Each area In Selection.Areas
area = Evaluate(area.Address & "/1000")
Next
End Sub
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
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?
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.
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
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.