ranking with vba

[cecyTeran]

Hello im trying to do a ranking with vba this is the summary of my code :

Dim RR1 As Range
Dim RG1 As Range
Dim rule1 As Variant
rule1 = ComboBox5.value
Set RR1 = ActiveSheet.Range(ActiveSheet.Range("C4").End(xlToRight).Offset(1, 6), ActiveSheet.Range("C4").End(xlToRight).Offset(1, 6).End(xlDown))
'rangetorank2 = ActiveSheet.Range(ActiveSheet.Range("C4").End(xlToRight).Offset(1, 7), ActiveSheet.Range("C4").End(xlToRight).Offset(1, 7).End(xlDown))
Set RG1 = ActiveSheet.Range(ActiveSheet.Range("C4").End(xlToRight).Offset(1, 9), ActiveSheet.Range("C4").End(xlToRight).Offset(1, 9).End(xlDown))
If rule1 = "higher is better" Then
RR1.Select
With Range("RR1").Offset(0, 3)
'.FormulaArray = "=Rank(z5:z7,z5:Z7,0)" (doing this works perfectly but as the table could have more values and not necesary will be in the Z column y was trying to run the following code but have different errors)
.FormulaArray = "=Rank((" & Range("RR1").Address & "),(" & Range("RR1").Address & ",0)"
.FormulaArray = "=Rank(RR1,RR1,0)"
(none of the two formulas above works )
End With
ElseIf ComboBox5.value = "lower is better" Then
End If
End Sub

thanks you in advance if you can give me an idea

 

[Truiz]

[B]FormulaArray = "=Rank(" & RR1.Address & "," & RR1.Address  & ",0)[/B]

Should work