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

Open in new window

CartilloAsked:
Who is Participating?
 
Rory ArchibaldCommented:
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

Open in new window


Note: it is normal to indent code the opposite way to the way you did your End Ifs.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
 
Rob HensonFinance 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
jimyXCommented:
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

Open in new window


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

Open in new window

0
 
Rory ArchibaldCommented:
Actually, just:
mem = Mid(Cells(41,3).value, 8)

Open in new window


should do it.
0
 
Saqib Husain, SyedEngineerCommented:
Beauty!!!!!!, Rory
0
 
Rory ArchibaldCommented:
Credit to Rob, really - until his post I hadn't even looked at what the code was actually doing!
0
 
Rob HensonFinance AnalystCommented:
Thanks Rory!!
0
 
CartilloAuthor Commented:
Hi All,

Thanks a lot for the help
0
 
Saqib Husain, SyedEngineerCommented:
Cartillo, you missed THE BEST solution
0
 
RamanhpCommented:
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.

All Courses

From novice to tech pro — start learning today.