Range("R6").value = Range("Q18").Value

Range("T6").Value = Range("P18").Value

Solved

Posted on 2014-08-11

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

14 Comments

Range("R6").value = Range("Q18").Value

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.

http://www.geeksengine.com

but I'm not even sure what I am asking for can be done (but I thought the same for a blinking command button)

```
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
```

```
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
```

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

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
```

```
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
```

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.

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Advanced finding in Excel | 9 | 17 | |

Generating Excel in php | 5 | 30 | |

find phone numbers in excel | 6 | 20 | |

still having troubles with an Excel Macro saving to C: on Windows 10 | 5 | 20 |

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

Connect with top rated Experts

**18** Experts available now in Live!