Results 1 to 6 of 6

If, Elseif, Else statements for a UDF

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-15-2013
    Location
    London
    MS-Off Ver
    Excel 2003, 2007, 2010, 2019
    Posts
    436

    If, Elseif, Else statements for a UDF

    Hello everyone,

    I wanted to converted numeric values in a column to rating scales from 0 - 10. The scheme is:

    ER < -10.0% 0
    -10.0% <= ER < -5.0% 1
    -5.0% <= ER < -2.0% 2
    -2.0% <= ER < -0.5% 3
    -0.5% <= ER < -0.1% 4
    -0.1% <= ER < 0.1% 5
    0.1% <= ER < 0.5% 6
    0.5% <= ER < 2.0% 7
    2.0% <= ER < 5.0% 8
    5.0% <= ER < 10.0% 9
    ER >= 10.0% 10

    I created a UDF as:
    
    Function Num2Cat(rCell As Range)
    
        Dim v As Double, c As Integer
        
        v = rCell.Value
        
        On Error GoTo ErrorHandler
        
        If Not IsNumeric(v) Then Exit Function
            
        If v < -0.1 Then
            c = 0
        ElseIf v >= -0.1 And v < -0.05 Then
            c = 1
        ElseIf v >= -0.05 And v < -0.02 Then
            c = 2
        ElseIf v >= -0.02 And v < -0.005 Then
            c = 3
        ElseIf v >= -0.005 And v < -0.001 Then
            c = 4
        ElseIf v >= -0.001 And v < 0.001 Then
            c = 5
        ElseIf v >= 0.001 And v < 0.005 Then
            c = 6
        ElseIf v >= 0.005 And v < 0.02 Then
            c = 7
        ElseIf v >= 0.02 And v < 0.05 Then
            c = 8
        ElseIf v >= 0.05 And v < 0.1 Then
            c = 9
        Else
            c = 10
        End If
        
        Num2Cat = c
        
    ErrorHandler:
            Exit Function
       
    End Function
    But is there a better way to define this UDF for my purpose? I feel the If, Elseif, Else statements are not that tidy at all.

    Many thanks in advance.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] When ElseIf condition is met, the statements under elseif are not happening.
    By excelkann in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-07-2016, 12:40 PM
  2. Code stepping in to elseif statement when elseif is definitely not true
    By DFrank231 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-13-2013, 05:31 PM
  3. [SOLVED] If Else and ElseIf statements
    By paulary30 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-10-2013, 04:44 PM
  4. [SOLVED] VBA: Use Array instead of using ElseIf statements
    By Hudas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2012, 12:44 PM
  5. If Elseif elseif problem
    By tjbillund in forum Excel General
    Replies: 3
    Last Post: 08-10-2011, 07:02 AM
  6. Ordering If, Else, and ElseIf Statements in VBA
    By BrownTeddyBear in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-07-2009, 01:51 AM
  7. [SOLVED] Re: IF..Then..ELSE.. ELSEIF
    By rjamison in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2005, 08:05 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