Calculate with code

[Chey]

I have this code


Private Sub Worksheet_Calculate(ByVal Target As Range)
Const WS_RANGE As String = "b47" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If UCase(.Value) = "#" Then
.Value = (7.15 * b47)
.NumberFormat = "#"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

I am wanting to type a number for example 160 in b47 and it multiply by 7.15
and return the answer in B47.

Thanks
Chey

 

[Dave Peterson]

First, you can't just change those events and expect them to work.

I think you want something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "b47" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = 7.15 * .Value
.NumberFormat = "#.##" '<-- I changed this
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[Don Guillett]

Try this. Of course, in the sheet module.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$47" Then Exit Sub

Application.EnableEvents = False
Target = Target * 10
Application.EnableEvents = True
End Sub