Simplify the formula

Hi Experts,

I would like to request Experts help. How to simply the attached code to allow me to use just one "End If"
``````Dim mem As Variant

If Len(Cells(41, 3)) = 12 Then
mem = Mid(Cells(41, 3), 8, 4)
Else
If Len(Cells(41, 3)) = 14 Then
mem = Mid(Cells(41, 3), 8, 6)
Else
If Len(Cells(41, 3)) = 16 Then
mem = Mid(Cells(41, 3), 8, 8)
Else
If Len(Cells(41, 3)) = 10 Then
mem = Mid(Cells(41, 3), 8, 2)
Else
If Len(Cells(41, 3)) = 11 Then
mem = Mid(Cells(41, 3), 8, 3)
Else
If Len(Cells(41, 3)) = 15 Then
mem = Mid(Cells(41, 3), 8, 7)

End If
End If
End If
End If
End If
End If
``````
Who is Participating?

Commented:
Or use ElseIf:

``````Dim mem As Variant

If Len(Cells(41, 3)) = 12 Then
mem = Mid(Cells(41, 3), 8, 4)
ElseIf Len(Cells(41, 3)) = 14 Then
mem = Mid(Cells(41, 3), 8, 6)
ElseIf Len(Cells(41, 3)) = 16 Then
mem = Mid(Cells(41, 3), 8, 8)
ElseIf Len(Cells(41, 3)) = 10 Then
mem = Mid(Cells(41, 3), 8, 2)
ElseIf Len(Cells(41, 3)) = 11 Then
mem = Mid(Cells(41, 3), 8, 3)
ElseIf Len(Cells(41, 3)) = 15 Then
mem = Mid(Cells(41, 3), 8, 7)
End If
``````

Note: it is normal to indent code the opposite way to the way you did your End Ifs.
0

Microsoft MVP ExcelCommented:
Hello,

use a select statement.

Select case Len(Cells(41, 3))
case 12
mem = Mid(Cells(41, 3), 8, 4)
case 14
mem = Mid(Cells(41, 3), 8, 6)
case 16
mem = Mid(Cells(41, 3), 8, 8)

' and so on
end select

cheers, teylyn
0

Finance AnalystCommented:
Alternatively, you can simplify it, if I am interpreting the code correctly, that you are extracting from the text all but the first 8 characters. Therefore it would be the same as doing:

mem = Right(Cells(41,3), Len(Cells(41,3))-8)

Cheers
Rob H
0

Commented:
I am not sure if there is "and" in VBA but please try either this way:
``````Dim mem As Variant
If (Len(Cells(41, 3)) >= 12) and (Len(Cells(41, 3)) <= 15) Then
mem = Mid(Cells(41, 3), 8, Len(Cells(41, 3)-8)
End If
``````

Or, this way:
``````Dim mem As Variant
If (Len(Cells(41, 3)) >= 12) Then
If (Len(Cells(41, 3)) <= 15) Then
mem = Mid(Cells(41, 3), 8, Len(Cells(41, 3)-8)
End If
End If
``````
0

Commented:
Actually, just:
``````mem = Mid(Cells(41,3).value, 8)
``````

should do it.
0

EngineerCommented:
Beauty!!!!!!, Rory
0

Commented:
Credit to Rob, really - until his post I hadn't even looked at what the code was actually doing!
0

Finance AnalystCommented:
Thanks Rory!!
0

Author Commented:
Hi All,

Thanks a lot for the help
0

EngineerCommented:
Cartillo, you missed THE BEST solution
0

Commented:
this was indeed an interesting one line solution given by Rorya, and that i look forward in the first go

great job :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.