Shorter code?

[Belleke]

Case Is = 1
        T_00.Value = WorksheetFunction.Max([Nr_1]) + 1
    Case Is = 2
        T_00.Value = WorksheetFunction.Max([Nr_2]) + 1
    Case Is = 3
        T_00.Value = WorksheetFunction.Max([Nr_3]) + 1
    Case Is = 4
        T_00.Value = WorksheetFunction.Max([Nr_4]) + ?
    Case Is = 5
        T_00.Value = WorksheetFunction.Max([Nr_5]) + 1

T_00 is a textbox in a userform, Case IS= 1 is the name of a worksheet [Nr_1] is a defined name range.
My question is can this code shorter,

 

[Debaser]

What does + ? mean in case 4? If you use Evaluate, you can pass a string and just append T_00 to "Nr_"

 

[Belleke]

+ ? is a typo, it should be +1

 

[Belleke]

Can you give what more explenation, afther beiing in the hospital for almost a year, i lost a lot of my knowledge. I just want to help a friend with his fishing competition.

 

[vletm]

Belleke​

pure guess... without Select
T_00.Value = WorksheetFunction.Max(["Nr_" & Is]) + 1

 

[Marc L]

My question is can this code shorter

Not such a good idea to post a partial code without the necessary condition expression ‼​

​

In order to remove all the Select Case block, as guessing can't be coding​

so you must replace ¤ with what is used in the original missing condition expression :​

​

If ¤ > 0 And ¤ < 6 Then T_00 = Application.Max(Range("Nr_" & ¤)) + 1​

​

​

As the original code should be like​

    Case 1:  T_00 = [MAX(Nr_1)+1]
    Case 2:  T_00 = [MAX(Nr_2)+1]

​

so to shorten, an Excel formula VBA variation : If ¤ > 0 And ¤ < 6 Then T_00 = Evaluate("MAX(Nr_" & ¤ & ")+1")​

​

Or via Case 1 To 5 if any other element within the Select Case missing condition expression block …​

​

 

[Belleke]

@ MarcL, thank you for your code, works perfect.