Extracting agruments from a function
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
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
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 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
ASKER
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.
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.
ASKER
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)
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)
ASKER
If I've confused you please let me know.
Try this:
Dim strFormulaData As String
Dim strParts() As String
strFormulaData = Replace(Range("A1").Formula, "=RANK.EQ(", "")
strFormulaData = Replace(strFormulaData, ")", "")
strParts = Split(strFormulaData, ",")
Range("R6").Value = Range(strParts(0)).Value
Range("T6").Value = Range(strParts(2)).ValueASKER
Here is the code I have in my Worksheet_Change Event:
The only way out is to go to Tsk Manager and End the Application.
What have I done wrong now?
Thanks
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strFormulaData As String
Dim strParts() As String
On Error GoTo Worksheet_Change_Error
strFormulaData = Replace(Range("S6").Formula, "=RANK.EQ(", "")
strFormulaData = Replace(strFormulaData, ")", "")
strParts = Split(strFormulaData, ",")
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 SubThe only way out is to go to Tsk Manager and End the Application.
What have I done wrong now?
Thanks
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.
ASKER
thank you
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?
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 strFormulaData As String
Dim strParts() As String
Application.EnableEvents = False
On Error GoTo Worksheet_Change_Error
If InStr(1, Range("S6").Formula, "=RANK.EQ(") > 0 Then
strFormulaData = Replace(Range("S6").Formula, "=RANK.EQ(", "")
strFormulaData = Replace(strFormulaData, ")", "")
strParts = Split(strFormulaData, ",")
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 SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Testing now. Adding to Activate Event - back shortly and then a question on the Dashboard w/s
ASKER
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.
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.
What about this that I asked above?
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?
ASKER
Thank you - you did a great job again!
Range("R6").value = Range("Q18").Value
Range("T6").Value = Range("P18").Value