In order to avoid the user typing = before they enter an arithmetic operation in a cell (i.e. 5+2, 8*8, etc) I have this code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("C4:C303")) Is Nothing Then
If Target = "" Then Exit Sub
Application.EnableEvents = False
TempTarget = Target
Target = "=" & TempTarget
If IsError(Target) Then Target = TempTarget
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
It works perfectly for additions (5+5) and multiplications (9*55), but doesn't work well for divisions and subtractions with small numbers (5-2, 8/9) because Excel treats them as dates. It works well for subtractions and divisions with bigger numbers (that couldn't be dates). I want to have in the cell the arithmetic formula but displaying the result. How can I fix the code so that it always work the way I want?
This isn't as straight-forward to solve as it might appear. If you enter values like 5-2 or 8/9 there's no way to intercept the change taking place to the cell before Excel changes it into a Date format.
However there is a solution, although it may not suit your purpose.
Set the Format of each of the cells you want to apply this to (presumably C4:C303) to "Text" or "@".
Change your code to include the addition of Target.NumberFormat = "General" before setting the value of the cell.
The code to achieve that would look like this:
...
Application.EnableEvents = False
TempTarget = Target
' Add this line to change the format back to General
Target.NumberFormat = "General"
Target = "=" & TempTarget
...
This will then correctly handle cases like 5-2 or 8/9 that would otherwise become dates automatically.
Side Effect
This creates an undesired side effect that if one of the existing calculated cells is edited again later it will now be in "General" format instead of text.
This could be resolved by using the SelectionChange event and by setting the format of the cell to "Text" or "@" again if it's inside the range you are working in, but you'd want to undo that if the user doesn't edit the value of the cell.
It's a bit messy. Excel clearly doesn't lend itself to this type of approach.