[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Extracting agruments from a function

Posted on 2014-08-11
Medium Priority
120 Views
Folks,
Here is the syntax for the RANK.EQ function
RANK.EQ(number,ref,[order])
Here's an example of the function with cell references
``````=RANK.EQ(O18,O6:O26,P18)
``````
In this example O18 is the number within the ref list.
In this example O6:O26 is a list of test scores. In the syntax of the function this is represented by "ref".
In this example P18 is a option that represents the order.
The function is in cell S6
In cell R6 I would like that to be the number value that is in cell O18
In cell T6 I would like that to be the order value that is in cell P18
In the above example O18 = 54
In the above example P18 = 1
Therefore the values would look like this:
R6 = 54
S6 = the results of the function
T6 = 1
This is just an example and the values in the syntax for number and order will change.
I hope I've been clear
I've attached the w/b. The w/s is labeled RANKEQ
Excel-Statistics.xlsm
0
Question by:Frank Freese
• 8
• 6

LVL 50

Expert Comment

ID: 40254791
Why do you care about the formula. If you always want what's in Q18 and P18 can't you just do

Range("R6").value = Range("Q18").Value
Range("T6").Value = Range("P18").Value
0

Author Comment

ID: 40254835
This was just an example:
Range("R6").value = Range("Q18").Value (BTW - O18 not Q18)
Range("T6").Value = Range("P18").Value
These are dynamic values for the first and third arguments.

The function Range.Eq accepts 3 arguments.
The first argument is a number selected from an array; for example O6:O26 can have 26 listed numbers. I would like to extract the selected number from the formula and put that in R6 (it could be any value in O6:O26).
The second argument is the array it self; for example O6:O26. I do not need that value.
The third argument, although optional, can be 0, blank (default) or 1.  I would like to extract that value and put it in T6. If argument is a blank then nothing is in T6.

Hope this helps.
0

Author Comment

ID: 40254851
I found this:
http://www.geeksengine.com/article/vba-function-multiple-values.html
but I'm not even sure what I am asking for can be done (but I thought the same for a blinking command button)
0

Author Comment

ID: 40254865
If I've confused you please let me know.
0

LVL 50

Expert Comment

ID: 40254866
Try this:

``````Dim strFormulaData As String
Dim strParts() As String

Range("R6").Value = Range(strParts(0)).Value
Range("T6").Value = Range(strParts(2)).Value
``````
0

Author Comment

ID: 40255836
Here is the code I have in my Worksheet_Change Event:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim strParts() As String

On Error GoTo Worksheet_Change_Error

Range("R6").Value = Range(strParts(0)).Value
Range("T6").Value = Range(strParts(2)).Value

On Error GoTo 0
Exit Sub
Worksheet_Change_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Worksheet_Change of VBA"

End Sub
``````
Here is the error I receive:

The only way out is to go to Tsk Manager and End the Application.
What have I done wrong now?
Thanks
0

LVL 50

Expert Comment

ID: 40255979
I did my testing in a new workbook. Let me put the code in the Statistics workbook you attached and I'll take a look.
0

Author Comment

ID: 40256004
thank you
0

LVL 50

Expert Comment

ID: 40256066
Here's is corrected code which contains basically two changes.
1) Setting Application.EnableEvents to False so that the change in the workbook stemming from the change that the code causes to happen does not trigger another change event.

2) Handling the situation where there is no formula in S6 which was the reason you got the error.

Did you mean to remove the BlackRed code? And your template apparently has On Error Goto 0 just before exiting the sub and that's not needed.

And finally, the code will put data in the two cells only if RANK.EQ is in the formula. What do you want to happen if a completely different function is used?
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim strParts() As String

Application.EnableEvents = False
On Error GoTo Worksheet_Change_Error
If InStr(1, Range("S6").Formula, "=RANK.EQ(") > 0 Then

Range("R6").Value = Range(strParts(0)).Value
Range("T6").Value = Range(strParts(2)).Value
End If
Application.EnableEvents = True

On Error GoTo 0
Exit Sub
Worksheet_Change_Error:
Application.EnableEvents = True
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Worksheet_Change of VBA"

End Sub
``````
0

LVL 50

Accepted Solution

Martin Liss earned 2000 total points
ID: 40256088
See above for comments, but to be on the safe side in case the formula does not contain all uppercase, I added code that makes it upper case within the code.
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim strParts() As String

Application.EnableEvents = False
On Error GoTo Worksheet_Change_Error
If InStr(1, UCase(Range("S6").Formula), "=RANK.EQ(") > 0 Then

Range("R6").Value = Range(strParts(0)).Value
Range("T6").Value = Range(strParts(2)).Value
End If
Application.EnableEvents = True

On Error GoTo 0
Exit Sub
Worksheet_Change_Error:
Application.EnableEvents = True
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Worksheet_Change of VBA"

End Sub
``````
0

Author Comment

ID: 40256106
Testing now. Adding to Activate Event - back shortly and then a question on the Dashboard w/s
0

Author Comment

ID: 40256246
When I don't think something is possible you make it so!
Everything works - fantastic.
I am not using the BlackRed code at this time. This workbook is wide-open and I can not test for every possible combinations people can want to try.  As I continue to updated this workbook I may see a need to use it. That's why I kept the module.
The On Error Goto 0 is a left over given to me - I never did know why I needed that. Time to remove it.
I'm posting a new question on the Dashboard w/b - having problems going to a Topic w/s once selected.
0

LVL 50

Expert Comment

ID: 40256258
And finally, the code will put data in the two cells only if RANK.EQ is in the formula. What do you want to happen if a completely different function is used?
0

Author Closing Comment

ID: 40256262
Thank you - you did a great job again!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.