Need help creating a user defined function in Excel with VBA

Question

I'm totally stuck on creating a user defined function in Excel VBA for the following problem. Any help would be greatly appreciated.

My excel file looks like this (only adding a small portion for the sake of brevity):

       A          B          C          D
1     Susan     Reagan     Smith         
2     Jill        L        Taylor
3     Sarah     Sullivan   Williams
4     Roger       J        Lopez

I would like a function that determines: If A1 = Susan OR Jill OR Sarah, AND LEN (length of string) of B1 >1 THEN D1 = A1 /2 B1 C1 ELSE A1 /2 C1

So the output in column D would look like:

       A          B          C          D
1     Susan     Reagan     Smith      Susan /2 Reagan Smith      
2     Jill        L        Taylor     Jill /2 Taylor
3     Sarah     Sullivan   Williams   Sarah /2 Sullivan Williams 
4     Roger       J        Lopez      Roger /2 Lopez

I want to use VBA because I have many names to add and don't really want a huge formula in cell D1 with a lot of nested if statements. I've been working on it myself but it's just a mess and I'm too embarrassed to post it here. Thanks guys!

phoog · Accepted Answer · 2011-04-08 15:38:26Z

3

Public Function JoinNames(A, B, C)
    If FirstNameMatches(A) And Len(B) > 1 Then
        JoinNames = A & " /2 " & B & " " & C
    Else
        JoinNames = A & " /2 " & C
    End If
End Function

Private Function FirstNameMatches(N) As Boolean
    Select Case N
        Case "Susan", "Jill", "Sarah"
            FirstNameMatches = True
    End Select
End Function

answered Apr 8, 2011 at 15:38

phoog

43.2k6 gold badges84 silver badges125 bronze badges

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

Comments

GSerg · Accepted Answer · 2011-04-08 15:33:40Z

0

Have a list of names somewhere else on a sheet.

Use IF(ISNA(MATCH(A1, list_of_names, 0)), "Not found", "Found") to figure out if the name is recognized.

answered Apr 8, 2011 at 15:33

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Collectives™ on Stack Overflow

Need help creating a user defined function in Excel with VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related