0

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

enter image description here

SHEET SOURCE

enter image description here

roy
  • 693
  • 2
  • 11
  • @braX , I update the vba code but I want the vba array code so that it becomes faster and also before the process is done then first sort the date in the source sheet – roy Feb 18 '22 at 07:02
  • You can [take date into array](https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba/52467171) and then [sort the array](https://stackoverflow.com/questions/152319/vba-array-sort-function) – Foxfire And Burns And Burns Feb 18 '22 at 08:54
  • @FoxfireAndBurnsAndBurns , thanks to your reply, can you answer in response from you changing my vba code or formula into vba array – roy Feb 18 '22 at 09:08

0 Answers0