RE: [ExcelVBA] my first try with a function

 



Thank you all, David Smart, David Grugeon, and Derek Turner!

 

I want to learn and understand what I am doing so I tried each of your suggestions. The only one that I could get to work is Derek's.

 

Derek, your solution is so similar to what I had, with the exception of the variable names, that I am having trouble understanding what mine lacked. Can you help me understand?

 

Pam

 

-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]On Behalf Of Derek Turner
Sent: Wednesday, October 02, 2013 5:30 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] my first try with a function

Dear Pam

First you need something within the brackets that the function is evaluating. 

Then you need to give the function a return value.

So :-

Function DeptName(vDeptName As Variant) As String

Dim sReturnValue As String

    Select Case vDeptName

        Case 1: sReturnValue = "beads"

        Case 2: sReturnValue = "belt"

        ' Case etc

        Case Else: sReturnValue = "not found"

    End Select

    DeptName = sReturnValue

End Function

Let me know how you get on with this.

Regards

Derek Turner

England

+++

---

From: Pam <pstryk24@gmail.com>
To: ExcelVBA@yahoogroups.com
Sent: Wednesday, 2 October 2013, 22:40
Subject: [ExcelVBA] my first try with a function

 

I've been doing cell formulas in excel for years but this is the first time
I have tried to do a function. Of course, it did not work.

Here is my situation.

Column K, rows 2:100, have a number, mostly between 1 and 25 in it. If the
cell has anything else in it I want it to be ignored. Perhaps the problem is
in the way I tried to call the function.

The function I tried:

Function deptName() As String
Select Case dn
Case 1
dn = "beads"
Case 2
dn = "belt"
Case 3
dn = "bolo"
Case 4
dn = "clock"
Case 6
dn = "concho"
Case 7
dn = "cords"
Case 8
dn = "dolls"
Case 9
dn = "floral"
Case 10
dn = "general"
Case 12
dn = "holiday"
Case 14
dn = "jewelry"
Case 15
dn = "kits"
Case 16
dn = "light"
Case 17
dn = "pattern"
Case 18
dn = "miniatures"
Case 19
dn = "music"
Case 20
dn = "pc"
Case 21
dn = "rhinestones"
Case 22
dn = "sequin"
Case 23
dn = "sewing"
Case 24
dn = "chimes"
Case 25
dn = "wood"

End Select

End Function

In column L, I tried calling it with =deptName(k2)
I got a #value! error.

I looked around online but didn't find anything that could help me clearly
understand how to call the function.

Would someone have a moment to steer me straight.

Thanks!

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (79)

Recent Activity:

• New Members 1

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___