+ Reply to Thread
Results 1 to 2 of 2

VBA Case statements

  1. #1
    Registered User
    Join Date
    07-19-2015
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    1

    VBA Case statements

    I have written the program to classify nos 1-4 as short, 4-8 as medium and 9,10 as long. The output shows all nos as long. Please help



    Sub casestatements()
    Dim filmname As String
    Dim filmlength As Integer
    Dim filmtype As String

    filmname = Application.InputBox("Select the film", Type:=0)
    filmlength = ActiveCell.Offset(0, 1).Value

    Select Case filmlength

    Case 1, 2, 3, 4
    filmtype = "short"
    Case 5, 6, 7, 8
    filmtype = "medium"

    Case Else
    7 filmtype = "Long"

    End Select

    MsgBox filmname & " is " & filmtype

    End Sub

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: VBA Case statements

    This works for me -

    Please Login or Register  to view this content.
    I hard coded values in the filmlength variable while testing though..

    And is this a Typo? 7 filmtype

    Besides, I am not convinced Application.InputBox is the way to go.. And ActiveCell.Offset will always refer to the value to the corresponding column of the activecell, irrespective of what filmname you type, as you have referred to 1 Column Offset of the Activecell.. Which according to me is causing the problem.
    Last edited by NeedForExcel; 07-19-2015 at 03:03 AM.
    Cheers!
    Deep Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Case Statements and Declarations
    By Grilleman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2015, 08:44 PM
  2. Using ComboBox values for case statements
    By dylcon in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-10-2013, 09:21 AM
  3. [SOLVED] Case Statements and If Statements
    By hawsoon13 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-05-2012, 12:44 PM
  4. help with if then select case statements
    By timermel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-26-2011, 05:35 PM
  5. Multiple case statements
    By abhijit.pendharkar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-28-2009, 04:12 AM
  6. Case Statements
    By anazarian in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-08-2008, 06:11 AM
  7. Select Case Statements
    By ladeda063610 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2008, 11:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1