Excel VBA question - If then ElseIf statement

Question

I have a VBA conditional function I hacked together (I'm a noob) that checks for a name in a cell and then returns appropriate variations if one of the conditions is true, otherwise it returns a blank "". Instead of returning a blank, I'd like it to return the default cell value.

As an example, I have the following cells and results based on my function:

   Cells
   A        B
1  Bob      Bob Rob Robert
2  Mike     Mike Michael
3  Dan      Dan Daniel
4  Scott

I'd like the result for B4 to return the default value in A4 (Scott), rather then a blank, like this:

   Cells
   A        B
1  Bob      Bob Rob Robert
2  Mike     Mike Michael
3  Dan      Dan Daniel
4  Scott    Scott

Any help would be appreciated:

Here's my function (abbreviated version without all names included in ElseIf):

Function NameList(pVal As String) As String

    If pVal = "Bob" Then
        NameList = "Bob Rob Robert"
    ElseIf pVal = "Mike" Then
        NameList = "Mike Michael"
    ElseIf pVal = "Dan" Then
        NameList = "Dan Daniel"
    Else
        NameList = ""
    End If

End Function

Thanks!

Hossein · Accepted Answer · 2011-03-16 19:14:55Z

6

I think
Else NameList = pVal
solves your problem.

answered Mar 16, 2011 at 19:14

Hossein

4,1652 gold badges26 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anders Lindahl · Accepted Answer · 2011-03-16 19:13:26Z

3

Take a good look at the else clause:

 [...]
 Else
     NameList = ""
 End If

The function returns the empty string ("") if none of the if/elseif clauses matches.

If your function is called with pVal="Scott" you fall through to the default assignment. What would you like it to be instead of the empty string?

answered Mar 16, 2011 at 19:13

Anders Lindahl

43.3k9 gold badges93 silver badges94 bronze badges

Comments

MPękalski · Accepted Answer · 2011-03-16 19:20:52Z

2

I do not know whether I understand your question correctly but try this

Function NameList(pVal As String) As String

If pVal = "Bob" Then
    NameList = "Bob Rob Robert"
ElseIf pVal = "Mike" Then
    NameList = "Mike Michael"
ElseIf pVal = "Dan" Then
    NameList = "Dan Daniel"
Else
    NameList = pVal
End If

End Function

answered Mar 16, 2011 at 19:20

MPękalski

7,1045 gold badges29 silver badges37 bronze badges

Collectives™ on Stack Overflow

Excel VBA question - If then ElseIf statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related