Loop through a named range and using application.vlookup, application.rank and store the results in an array

[Waimea]

I have crossposted a similar question at https://www.excelforum.com/excel-pr...lues-instead-of-looping-through-combobox.html.



I am trying to learn how to loop through a named range and a range and then using Application.Vlookup, Application.Rank and Application.Index, Application.Match on the named range/range.

At the moment I am using a for loop to iterate through an ActiveX ComboBox to perform Vlookups and I would like to run Application.Vlookup on a range instead to see if it is faster then accessing the value through the ComboBox.

MyRange contains 40 cities and I would like to Vlookup 5 values for each city, rank each value and then finally write the results to my spreadsheet, with the results of the vlookup, the rank and maybe index & match lookup also for more flexible lookups.

If possible I would also like to learn how to store the results in an array?


This code I got working and I understand it.

Sheets("Dash).Range("M18") = Application.WorksheetFunction.VLookup(Sheets("Dash").Range("F5"), Sheets("Data").Range("A1:PY305"), 2, False)

This code I don't understand?

Sub WantToBeLooped()

Dim ws As Worksheet
Dim i As Integer
Dim j As Integer
Dim myRange as Range

Set myRange = ???
Set ws = ThisWorkbook.Sheets("Dash")
Set ws1 = ThisWorkbook.Sheets("Data")

LastRow = ThisWorkbook.Sheets("Dash").Range("A" & Rows.Count).End(xlUp).Row



With ws
' I don't understand how this works
    For j = 1 To Lastrow
        For i = 1 To LastColumns
        On Error Resume Next
        
        Sheets("Dash).Range(i, j) = Application.WorksheetFunction.VLookup(Sheets("Dash").Range(myRange, i), Sheets("Data").Range("A1:PY305"), 2, False)
           
        Next i
    Next j
End With


End Sub

I am grateful for all help I can get!

 

[Waimea]

Sub Test()
Dim i As Long
Dim lr As Long

Dim R As Long
Dim WkArr


Application.ScreenUpdating = False


    lr = Sheets("Test").range("A" & Rows.Count).End(xlUp).Row
    R = Cells(Rows.Count, 1).End(xlUp).Row + 1
    ReDim WkArr(1 To 40, 1 To 4)
    For i = 2 To lr
    
    On Error Resume Next
    
    If Sheets("Test").CheckBox1 = True Then
        WkArr(i, 1) = Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 314, False) - Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 107, False)
        WkArr(i, 2) = Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 316, False) - Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 108, False)
        WkArr(i, 3) = Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 317, False) - Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 109, False)
        WkArr(i, 4) = Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Costs").range("A1:U322"), 14, False)
    
    ElseIf Sheets("Test").CheckBox2 = True Then
        WkArr(i, 1) = Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 314, False) - Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 107, False)
        WkArr(i, 2) = Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 316, False) - Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 108, False)
        WkArr(i, 3) = Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 317, False) - Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Data").range("A1:PY305"), 109, False)
        WkArr(i, 4) = Application.WorksheetFunction.VLookup(Sheets("Test").range("A" & i), Sheets("Costs").range("A1:U322"), 14, False)
    End If
                         
    Next i   


    With Sheets("Test")
        .Cells(R, 1).Resize(UBound(WkArr, 1), UBound(WkArr, 2)) = WkArr
    End With

Application.ScreenUpdating = True


End Sub

Now I think I have got it working, how can I use Application.WorksheetFunction.Rank on the array?

Also, Is there a better way to do what I am trying to do with the checkboxes. Checked means that I am including it, unchecked means that I am excluding it.

 

[Waimea]

It seems like the best way is to drop the array on the worksheet and then ranking it.

I am trying to loop through all possible combinations of Vlookup in my range.

My range is in ("A2:A41) and I am trying to assign the value of the range to be my array.

Can someone give me some help on how to proceed?