Link to home
Start Free TrialLog in
Avatar of Cartillo
CartilloFlag for Malaysia

asked on

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

SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Actually, just:
mem = Mid(Cells(41,3).value, 8)

Open in new window


should do it.
Beauty!!!!!!, Rory
Credit to Rob, really - until his post I hadn't even looked at what the code was actually doing!
Thanks Rory!!
Avatar of Cartillo

ASKER

Hi All,

Thanks a lot for the help
Cartillo, you missed THE BEST solution
this was indeed an interesting one line solution given by Rorya, and that i look forward in the first go

great job :)