# Shorter code?

#### Belleke

##### Well-Known Member
Code:
``````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,

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

+ ? is a typo, it should be +1

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.

#### Belleke​

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

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​
Code:
``````    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 …​

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