Calculate with code
 

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

Calculate with code
 

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



Chey wrote:

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

Calculate with code
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chey" wrote in message
...
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