adding array elements to vba range variable

[kenster]

Dim DecRND(12) As Variant
    DecRND(1) = Rnd(): DecRND(2) = Rnd(): DecRND(3) = Rnd(): DecRND(4) = Rnd()
    DecRND(5) = Rnd(): DecRND(6) = Rnd(): DecRND(7) = Rnd(): DecRND(8) = Rnd()
    DecRND(9) = Rnd(): DecRND(10) = Rnd(): DecRND(11) = Rnd(): DecRND(12) = Rnd()
'       This line assigns 12 different random numbers to each arry element  

    Dim myRange As Range
    Set myRange = Range("{DecRND(1),DecRND(2), DecRND(3),DecRND(4), DecRND(5),DecRND(6), DecRND(7),DecRND(8), DecRND(9),DecRND(10), DecRND(11),DecRND(12)}")
     
    If (f9_count = 0) Then
'      syntax  Application.WorksheetFunction.Index(Bound, row_num, column_num)
'      syntax  application.worksheetfunction.rank(RankLevel, Array, Order)
        f9_count = Application.WorksheetFunction.Index(Bound, Application.WorksheetFunction.Rank(DecRND(1), myRange, 0), 1)
        ws2.Range("$J$6").Value = f9_count
    End If

 

[Spyros13]

What's the question?

 

[kenster]

Hello Spyros13: The question is how to get the embedded RANK() function to accept its 2nd argument
without generating the type mismatch error. As it is now, the RANK() function wants a range or range
variable there (arg2), but all I have is a 12 element numerical (variant) array. To this point I have been
looking for a way to convert my 12 element array into a range (range variable). My VBA code compiles
but generates a runtime error at the Set myRange statement and the debugger opens and halts there. My
VBA macro requires both Index(Rank()) functions to work.

 

[Spyros13]

do any of these 2 methods help you? 1, Macro1 is by using a workaround ,Hiddensheet, to create a Range object from those cells, and then use the Range object in the Rank function., and the other other, Macro2 is by using a helper function to calculate the rank directly.

"Hiddensheet" Version:

Sub Macro1()

Dim DecRND(12) As Variant
Dim wsHidden As Worksheet
Dim myRange As Range
Dim indexRange As Range
Dim ws2 As Worksheet
Dim f9_count As Integer

f9_count = 0

Set ws2 = ThisWorkbook.Worksheets("Sheet3") 'Replace "Sheet3" here with the actual name of your sheet you are working on,
Set indexRange = Worksheets("Sheet1").Range("A1:A12") 'The range you're pulling from needs to be defined

Set wsHidden = ThisWorkbook.Worksheets("HiddenSheet")

For i = 1 To 12
    DecRND(i) = Rnd()
    wsHidden.Cells(i, 1).Value = DecRND(i) 'write values to the hidden sheet
Next i

Set myRange = wsHidden.Range("A1:A12")

If (f9_count = 0) Then
    f9_count = Application.WorksheetFunction.Index(indexRange, _
                Application.WorksheetFunction.Rank(DecRND(1), myRange, 0), 1)
    ws2.Range("$J$6").Value = f9_count
End If

End Sub

without needing a hudden sheet:

Sub Macro2()
    Dim DecRND(1 To 12) As Variant
    Dim i As Integer
    Dim f9_count As Integer
    Dim ws2 As Worksheet
    Dim indexRange As Range

    Set indexRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:A12")
    Set ws2 = ThisWorkbook.Worksheets("Sheet3")
  
    ' Populate the array with random numbers
    For i = 1 To 12
        DecRND(i) = Rnd()
    Next i

    ' Assuming f9_count = 0 at the start
    f9_count = 0
    If f9_count = 0 Then
        f9_count = Application.WorksheetFunction.Index(indexRange, _
                GetRank(DecRND(1), DecRND), 1)
        ws2.Range("$J$6").Value = f9_count
    End If
End Sub

HELPER FUNC FOR MACRO2

Function GetRank(num As Variant, arr() As Variant) As Integer
    Dim i As Integer
    Dim counter As Integer
  
    counter = 0
    For i = LBound(arr) To UBound(arr)
        If arr(i) <= num Then
            counter = counter + 1
        End If
    Next i
    GetRank = counter
End Function