Results 1 to 4 of 4

Case issues in VBA

Threaded View

ArenaNinja Case issues in VBA 10-12-2008, 04:28 PM
Kenneth Hobson Case comparisons will be 1 of... 10-12-2008, 05:08 PM
Leith Ross Hello ArenaNinja, Here is... 10-12-2008, 05:22 PM
ArenaNinja Thanks for the replies guys.... 10-12-2008, 06:03 PM
  1. #1
    Registered User
    Join Date
    04-18-2006
    Posts
    34

    Case issues in VBA

    Hello ladies and gentlemen. I have a rather frustrating issue that dates a few months back, so I hope to solve it today.

    The following code tests a value in Column A. If the value is between 1-5 or equal to 11, column B will return the string A. It works.
    Sub Numbers()
        Dim LastRow As Long, r As Long
        Dim strL As String
        
        LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
        Application.ScreenUpdating = False
        
        For r = LastRow To 5 Step -1
            If (Cells(r, "A").Value > 0 And Cells(r, "A").Value <= 5) Then
                strL = "A"
            ElseIf Cells(r, "A").Value = 11 Then
                strL = "A"
            End If
            
            Cells(r, "B") = strL
        Next r
        
        Application.ScreenUpdating = True
    End Sub
    And I believe the equivalent (replacing If with Select Case) goes:
    Sub Numbers()
        Dim LastRow As Long, r As Long
        Dim strL As String
        
        LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
        Application.ScreenUpdating = False
        
        For r = LastRow To 5 Step -1
            Select Case Cells(r, "A").Value
                Case Cells(r, "A").Value > 0 And Cells(r, "A").Value <= 5
                    strL = "A"
                Case Cells(r, "A").Value = 11
                    strL = "A"
            End Select
            
            Cells(r, "B") = strL
        Next r
    
        Application.ScreenUpdating = True
    End Sub
    However, if I run the second code with matching values in Column A (11, 1, 3, etc), nothing happens. Well, maybe something happens, but the values in Column B remain blank.

    Could anybody tell me why?
    Last edited by ArenaNinja; 10-12-2008 at 06:03 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Spellnumber
    By Williams in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 02-16-2020, 03:34 AM
  2. number to words
    By vjn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-05-2008, 04:57 AM
  3. Numbers to Text
    By sachinattri in forum Excel General
    Replies: 6
    Last Post: 06-15-2008, 03:07 AM
  4. "Translating" numbers into words
    By Portuga in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2008, 11:44 AM
  5. Spell number
    By nowfal in forum Excel General
    Replies: 4
    Last Post: 08-20-2007, 04:21 PM

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