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
=RANK.EQ(O18,O6:O26,P18)

Open in new window

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
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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
Frank FreeseAuthor Commented:
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
Frank FreeseAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Frank FreeseAuthor Commented:
If I've confused you please let me know.
0
Martin LissOlder than dirtCommented:
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)).Value

Open in new window

0
Frank FreeseAuthor Commented:
Here is the code I have in my Worksheet_Change Event:
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 Sub

Open in new window

Here is the error I receive:
Error 9
The only way out is to go to Tsk Manager and End the Application.
What have I done wrong now?
Thanks
0
Martin LissOlder than dirtCommented:
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
Frank FreeseAuthor Commented:
thank you
0
Martin LissOlder than dirtCommented:
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 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 Sub

Open in new window

0
Martin LissOlder than dirtCommented:
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 strFormulaData As String
Dim strParts() As String

Application.EnableEvents = False
On Error GoTo Worksheet_Change_Error
If InStr(1, UCase(Range("S6").Formula), "=RANK.EQ(") > 0 Then
    strFormulaData = Replace(UCase(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 Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
Testing now. Adding to Activate Event - back shortly and then a question on the Dashboard w/s
0
Frank FreeseAuthor Commented:
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
Martin LissOlder than dirtCommented:
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?
0
Frank FreeseAuthor Commented:
Thank you - you did a great job again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.