[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simplify the formula

Posted on 2011-04-19
11
Medium Priority
?
285 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Cartillo
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 400 total points
ID: 35423555
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 800 total points
ID: 35423587
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
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 400 total points
ID: 35423599
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Assisted Solution

by:jimyX
jimyX earned 400 total points
ID: 35423610
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35423624
Actually, just:
mem = Mid(Cells(41,3).value, 8)

Open in new window


should do it.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35423813
Beauty!!!!!!, Rory
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35423876
Credit to Rob, really - until his post I hadn't even looked at what the code was actually doing!
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 35424041
Thanks Rory!!
0
 

Author Closing Comment

by:Cartillo
ID: 35424418
Hi All,

Thanks a lot for the help
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35424746
Cartillo, you missed THE BEST solution
0
 
LVL 9

Expert Comment

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

great job :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question