'want to convert formula to vba array

I want to change the formula to vba array so that it is faster and if using the formula becomes slow for that many records then I have to sort the date in the source sheet. Is there a recommendation that's best? Thanks jack

'sheet "MASTER" from B2
=LOOKUP(9^9,SOURCE!$B$2:$B$10/(SOURCE!$A$2:$A$10=A2),SOURCE!$B$2:$B$10)
'sheet "MASTER" from C2
=LOOKUP(9^9,SOURCE!$B$2:$B$10/(SOURCE!$A$2:$A$10=A2),SOURCE!$C$2:$C$10)
Sub formulalookup()
        Dim V
        Application.ScreenUpdating = False
    With Sheets("MASTER").Range("b2:b" & Sheets("MASTER").UsedRange.Rows.Count)
       .Formula = "=LOOKUP(9^9,SOURCE!$B$2:$B$10/(SOURCE!$A$2:$A$10=A2),SOURCE!$B$2:$B$10)"
        V = .Value
       .Value2 = V
    End With
     With Sheets("MASTER").Range("c2:c" & Sheets("MASTER").UsedRange.Rows.Count)
       .Formula = "=LOOKUP(9^9,SOURCE!$B$2:$B$10/(SOURCE!$A$2:$A$10=A2),SOURCE!$C$2:$C$10)"
        V = .Value
       .Value2 = V
    End With
        Application.ScreenUpdating = True
End Sub

SHEET MASTER

SHEET SOURCE

arrays excel vba

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'want to convert formula to vba array

Sources

Related Questions