automate cell to divide by 1000

R

Rohit

Hi

i want to automate a cell/cells to be automatically divide by 1000

eg in cell A1, if i enter 1000,000 then the outcome should be 1,000.

Thanks
 
J

JE McGimpsey

Note that the custom format doesn't actually change the value/outcome
that's stored in the cell.

To actually change the value, one way is to choose Tools/Options/Edit

and change the Fixed decimal places input box to 3.


Note that this is a global setting and will affect all cells.

To change only A1, you could also use an event macro - post back if
you'd like to see that option.
 
G

Gord Dibben

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10"
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
cell.Value = cell.Value / 1000
Next cell
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the above into
that sheet module.

Edit to suit and Alt + q to return to Excel.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top