Excel Function questions 2

[121854]

I would like to create an excel function to check the value of a cell and if is equal to 180 - 395 the cell will
be populated with 1;if is equal to 730 - 100 the cell will be populated with 2; if is equal to 120 - 500 the cell will be populated with a 9.

I tried the vb code below but it does not return the value back to the calling cell.

Function xParse(myNum)
Dim pNum As Integer
If myNum = "180 - 395" Then
pNum = 1
myNum = pNum

End If

If myNum = "120 - 500" Then
myNum = "9"

End If


End Function

Any suggestions? Or guidance.

Thank you

 

[PHV]

Try something like this:
Public Function xParse(myNum)
Dim pNum
If myNum = "180 - 395" Then
pNum = 1
ElseIf myNum = "730 - 100" Then
pNum = 2
ElseIf myNum = "120 - 500" Then
pNum = 9
Else pNum = myNum
End If
xParse = pNum
End Function

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zathras]

...function to check the value of a cell and ... the cell will be populated with ...

It is not physically possible to have a function that replaces itself. You cannot have both a value like "120 - 500" and a function expression like =xParse(???) in the same cell. In the first place, what would go in ???. If the cell in question is B2 (for example) and you put the function =xParse(B2) in cell B2, you will have no room left over for "120 - 500" (Not to mention that you will have a circular reference.)

You need to look in the help file for proper declaration and coding of user-defined functions.

Assuming you understand that "120 - 500" needs to go into B1 and the formula =xParse(B1) would go into B2, then...

As PHV has indicated, the way to return a value from a function is to use the function name as if it were a variable and set it equal to the result.

Here is a slightly different approach to your situation since it fits the case construct quite nicely:

Function xParse(myNum)
  Select Case myNum
    Case "120 - 500": xParse = 9
    Case "730 - 100": xParse = 2
    Case "180 - 395": xParse = 1
    Case Else:        xParse = "invalid"
  End Select
End Function

 

[TonyJollans]

Hi 121854,

Assuming you have your original value in one cell and are looking to populate another cell based on that, you don't need a function at all. Just put this formula in the cell instead ..

[blue][tt]=LOOKUP(A3,{"120 - 500","180 - 395","730 - 100"},{9,1,2})[/tt][/blue]

(replacing A3 with your own cell reference)

If you really need a function in your cell for some reason, you must make the function RETURN something (see the last line in PHV's code) - it won't have any effect setting the passed value to something.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.