Wsrtjtyk

I have some VBA Code that works of a Control Button. The code work fine but I would like it to run automatically. I can't seem to get the Private Sub correct. Code is as follows:

Private Sub CommandButton3_Click()



Dim num As String



num = Range("L5").Value



Select Case num



        Case Is = 15, 16, 17, 18, 19



            Range("X5").Value = "510"



        Case Is = 20, 21, 22, 23, 24



            Range("X5").Value = "570"



        Case Is = 25, 26, 27, 28, 29



            Range("X5").Value = "610"



        Case Is = 30, 31, 32, 33, 34



            Range("X5").Value = "630"



        Case Is = 35, 36, 37, 38, 39



            Range("X5").Value = "635"



        Case Is = 40, 41, 42, 43, 44



            Range("X5").Value = "632"



        Case Is = 45, 46, 47, 48, 49



            Range("X5").Value = "622"



        Case Is = 50, 51, 52, 53, 54



            Range("X5").Value = "610"



        Case Is = 55, 56, 57, 58, 59



            Range("X5").Value = "590"



        Case Is = 60, 61, 62, 63, 64



            Range("X5").Value = "565"



        Case Is = 65, 66, 67, 68, 69



            Range("X5").Value = "540"



        Case Is = 70, 71, 72, 73, 74



            Range("X5").Value = "520"



        Case Is = 75, 76, 78, 79



            Range("X5").Value = "490"



        Case Is = 80, 81, 82, 83, 84



            Range("X5").Value = "470"



        Case Is = 85, 86, 87, 88, 89, 90



            Range("X5").Value = "440"



 End Select



 End Sub

You Don't need a case statement, you can do it in 1 line of code (plus an if to capture anything outside the range) because your brackets increment in a uniform way:

Public Function MyFunction2(ByVal MyValue As Double) As Double

If MyValue < 15 Or MyValue > 90 Then

    MyFunction2 = 0

Else

    MyFunction2 = CDbl(Array(0, 0, 0, 510, 570, 610, 630, 635, 632, 622, 610, 590, 565, 540, 520, 490, 470, 440)(Round(MyValue / 5, 0)))

End Function

It's a UDF so follow the same logic as ja72's answer but put MyFunction2 instead of MyFunction, put them side by side so you can compare that they both give the same results.

Create a user defined function and use it to update "X5" based on the contents of "L5"

In a module write the following code:

Public Function MyFunction(ByVal value As Double) As Double

    Dim x As Long, y As Long

    x = CLng(value)

    Select Case x

        Case 15 To 19

            y = 510

        Case 20 To 24

            y = 570

        Case 25 To 29

            y = 610

        Case 30 To 34

            y = 630

        Case 35 To 39

            y = 635

        Case 40 To 44

            y = 632

        Case 45 To 49

            y = 622

        Case 50 To 54

            y = 610

        Case 55 To 59

            y = 590

        Case 60 To 64

            y = 565

        Case 65 To 69

            y = 540

        Case 70 To 74

            y = 520

        Case 75 To 79

            y = 490

        Case 80 To 84

            y = 470

        Case 85 To 90

            y = 440

        Case Else

            y = 0

    End Select

    MyFunction = CDbl(y)

End Function

The result will update automatically.