Automatically divide by 1000 all numbers entered in a range?

[Fabulist]

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.

 

[Michael M]

How does it not work....the code provided by footoo in post #19 places the formula in each cell as you requested in post #14
We have provided a number of different options for you to try. In fact I can't think of many other ways to do it !!
Are you actually looking for the text "=500/1000", rather than the actual result ???

 

[Fabulist]

I meant that the macro does not function at all, not that it does not function as I described. Judging from how footoo wrote the code it should work exactly as I primarily asked it should work, but for some reason, it does not.

Nothing happens when you input numbers within the macro's range.

 

[footoo]

Has the macro been placed in the sheet module?

In case EnableEvents has been set to False, try running this :

Sub EnableE()
Application.EnableEvents=True
End Sub

 

[Fabulist]

You are correct, there was some kind of mishandle and Events did not reset to their proper state. The macro works exactly as I required it, many thanks for your time sir.

 

[Fabulist]

There is a problem, it is not possible to paste values only type them, the macro glitches out and stops functioning until the workbook is relaunched, is this a standard issue?

EDIT: Note, this happens with pasted values that come from formulas, for example pasting 5000 from a cell that has 5000 is viable, but pasting from a cell that has 5*1000 will create a debug issue.

 

[Fabulist]

Excuse me for replying again, I cannot edit the post. I believe there is an issue with commas ",", the macro cannot identify numbers that have inverted comma separation, for example 5,000.25 is fine but 5.000,25 is not ("," instead of ".").

My workbook / system works with the 5.000,25 method.

 

[footoo]

The cells in A1:G25 should be formatted as General or Number.
If anything other than a number is entered or pasted into A1:G25, either an error value will be returned (and the macro completes), or for some characters the macro will fail.

The reason you have to close and re-open is because the macro is failing before the line : Application.EnableEvents=True

What do you want to happen if anything entered or pasted is anything other than a number?

I cannot reproduce the problem using the info in your edit note.
Post an example of when the macro fails. Provide details :
- content and format of copied cell
- format of destination cell

 

[footoo]

Try this :

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
        If cel <> "" Then
            cel = Replace(cel, ".", "")
            cel = Replace(cel, ",", ".")
            cel.Formula = "=" & cel & "/1000"
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

 

[Fabulist]

Try this :

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
        If cel <> "" Then
            cel = Replace(cel, ".", "")
            cel = Replace(cel, ",", ".")
            cel.Formula = "=" & cel & "/1000"
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

I got a debug error because of the comma again, e.g. 500,25 will give the error. Any number with a comma will give an error.

 

[footoo]

I got a debug error because of the comma again, e.g. 500,25 will give the error. Any number with a comma will give an error.

I can't reproduce the error.
Both the macros in posts #9 and #28 work for me without error (with format #.##0,00 and with format #,##0.00).

You didn't reply to post #27 :

What do you want to happen if anything entered or pasted is anything other than a number?

I cannot reproduce the problem using the info in your edit note.
Post an example of when the macro fails. Provide details :
- content and format of copied cell
- format of destination cell