ExcelVBAでループを高速化する方法が必要

このようなものである可能性がありますが、インデックス（i、j、k）が混同されている可能性があります。入力と必要な出力（スクリーンショットなど）を投稿できれば、はるかに簡単になります

Sub FillTopSheet()

    'Declaring variables for counts
    Dim Flat_Row_Num As Long
    Dim Tower_Col_Num As Long
    Dim InventoryWs As Worksheet, Ws As Worksheet
    Dim ArrLookUp() As Variant, ArrData() As Variant
    Dim Lookup_Start_Row As Long, Lookup_End_Row As Long, i As Long, j As Long, k As Long
    Dim FlatNo As String

    'Designing a loop to move through the fill data on Top Sheet
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    'Debug.Print Application.Workbooks("The Crest DLF Project-In-Out Inventory Data.xlsx").Worksheets("Material-Out").Name
    Set InventoryWs = Application.Workbooks("The Crest DLF Project-In-Out Inventory Data.xlsx").Worksheets("Material-Out")

    Debug.Print Lookup_Start_Row
    Debug.Print Lookup_End_Row

    Lookup_Start_Row = 6162
    Lookup_End_Row = 14754

    Flat_Row_Num = 5
    Tower_Col_Num = 2 'Start in Tower A "Flats No. column

    With InventoryWs
        ArrLookUp = .Range(.Cells(Lookup_Start_Row, 6), .Cells(Lookup_End_Row, 8))
    End With

    With Ws
        ArrData = .Range(.Cells(Flat_Row_Num, Tower_Col_Num), .Cells(154, 13))
    End With

    For i = LBound(ArrData, 2) To UBound(ArrData, 2) Step 2
        For j = LBound(ArrData) To UBound(ArrData)
            'loop through "towers" Array
            FlatNo = ArrData(j, i) 'take one flat no
            If FlatNo <> "" Then
                For k = LBound(ArrLookUp) To UBound(ArrLookUp)
                    'look for this flat no in other array
                    If FlatNo = ArrLookUp(k, 3) Then
                        'first match = take Date from other array
                        'dates sorted descending
                        ArrData(j, i + 1) = ArrLookUp(k, 1)
                        'found what was looking for, get out of loop
                        Exit For
                    End If
                Next k
            End If
        Next j
    Next i

    With Ws
        'range must be same as when you set the array earlier. But if that range contains some formulas they'll be overwriten with values
        'in that case you can loop through array and take out only dates
        .Range(.Cells(Flat_Row_Num, Tower_Col_Num), .Cells(154, 13)) = ArrData
    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

編集
これはまだあなたの範囲に合うようにいくつかの調整が必要かもしれません範囲を
チェックしてくださいArrLookUp = .Range(.Cells(Lookup_Start_Row, 6), .Cells(Lookup_End_Row, 8))そしてArrLookUp = .Range(.Cells(Lookup_Start_Row, 6), .Cells(Lookup_End_Row, 8))
それは「タワー」を取り、「フラット番号」を探します。他のシートで。最初の試合日は他のシートから取得されます。