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

x
?
Solved

Extracting agruments from a function

Posted on 2014-08-11
14
Medium Priority
?
120 Views
Last Modified: 2014-08-12
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
0
Comment
Question by:Frank Freese
  • 8
  • 6
14 Comments
 
LVL 50

Expert Comment

by:Martin Liss
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

by:Frank Freese
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

by:Frank Freese
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Frank Freese
ID: 40254865
If I've confused you please let me know.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40254866
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
 

Author Comment

by:Frank Freese
ID: 40255836
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
 
LVL 50

Expert Comment

by:Martin Liss
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

by:Frank Freese
ID: 40256004
thank you
0
 
LVL 50

Expert Comment

by:Martin Liss
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 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
 
LVL 50

Accepted Solution

by:
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 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
 

Author Comment

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

Author Comment

by:Frank Freese
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

by:Martin Liss
ID: 40256258
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
 

Author Closing Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question