Excel only runs my sub procedure once?

[faeter87]

Can anyone please tell me why this will only run once?
In this sub procedure "F19" is an input cell and "F20" shows the calculated data. It does work, but unfortunately only once. After one input on the excel sheet, all the output cell will show is the previous answer. Why is that?


[VBA]Sub Reduction()

Dim x As Integer
x = Range("F19").Value

Select Case x
Case Is <= 400
Range("F20").Value = 1

Case 401 To 449
Range("F20").Value = (2.76 - (0.0044 * x))

Case 450 To 599
Range("F20").Value = (1.68 - (0.002 * x))

Case 600 To 699
Range("F20").Value = (1.92 - (0.0024 * x))

Case 700 To 799
Range("F20").Value = (1.08 - (0.0012 * x))

Case 800 To 899
Range("F20").Value = (0.44 - (0.0004 * x))

Case 900 To 1200
Range("F20").Value = (0.32 - (0.0002667 * x))

Case Is > 1201
Range("F20").Value = "NODATA"

End Select

End Sub[/VBA]

[Aflatoon]

You need to learn to use the VBA tags please.

How are you running the code - manually or automatically? Have you tried adding debugs or breakpoints to see if it is even running, or stepping through to test the values being used ( you haven't specified a sheet for either range for example)
Also is the output cell formatted to show decimal places?

[Kenneth Hobs]

Welcome to the forum! Click the VBA icon and paste code between tags. VBA tags is just one of the features that I like here.

It works for me. Here it is as a UDF. Of course autocalculation should be on for it to update as the intRange value changes.

[VBA]'=Reduction(F19)
Function Reduction(intCell As Range) As Variant
Dim x As Integer, dblVal As Variant
x = intCell.Value2

Select Case x
Case Is <= 400
dblVal = 1
Case 401 To 449
dblVal = (2.76 - (0.0044 * x))
Case 450 To 599
dblVal = (1.68 - (0.002 * x))
Case 600 To 699
dblVal = (1.92 - (0.0024 * x))
Case 700 To 799
dblVal = (1.08 - (0.0012 * x))
Case 800 To 899
dblVal = (0.44 - (0.0004 * x))
Case 900 To 1200
dblVal = (0.32 - (0.0002667 * x))
Case Is > 1201
dblVal = "NODATA"
End Select
Reduction = dblVal
End Function[/VBA]

[Aflatoon]

You could also use a lookup table and native formulas. Easier to maintain too.

[Bob Phillips]

Works for me too, and you can also use event code

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("F19")) Is Nothing Then

With Target

Select Case .Value
Case Is <= 400
.Offset(1, 0).Value = 1

Case 401 To 449
.Offset(1, 0).Value = (2.76 - (0.0044 * .Value))

Case 450 To 599
.Offset(1, 0).Value = (1.68 - (0.002 * .Value))

Case 600 To 699
.Offset(1, 0).Value = (1.92 - (0.0024 * .Value))

Case 700 To 799
.Offset(1, 0).Value = (1.08 - (0.0012 * .Value))

Case 800 To 899
.Offset(1, 0).Value = (0.44 - (0.0004 * .Value))

Case 900 To 1200
.Offset(1, 0).Value = (0.32 - (0.0002667 * .Value))

Case Is > 1201
.Offset(1, 0).Value = "NODATA"
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[faeter87]

Much appreciated, I learned a lot from you guys!

...And in the future I will use the VBA tags