Excel - Array & its size & loop

[wwj]

[Key Codes]

Dim kFactor As Variant
kFactor = Array(0.9, 0.8, 0.7, 0.6, 0.5)
maxIdxFt = UBound(kFactor) - LBound(kFactor) // Index = 0 up to maxIdxFt
sizeFt = UBound(kFactor) - LBound(kFactor) + 1 // Size of array.

Code: Select all

Sub adjust_Drain_K_TRUG()

    Dim kFactor As Variant
    Dim kSP As Variant
    
    ThisWorkbook.Activate
    
    drnSht = "TRUGrev_1a"
    
    ' Adjust K for drains.
    maxRow = Sheets(drnSht).Cells(Rows.Count, "A").End(xlUp).Row
    
    initK = 1#
    kSP = Array(81, 85, 89, 93, 97)
    kFactor = Array(0.9, 0.8, 0.7, 0.6, 0.5)
        
    For i = 3 To maxRow
        
        sp = Sheets(drnSht).Cells(i, "K")
        maxIdxFt = UBound(kFactor) - LBound(kFactor)
        
        For j = 0 To maxIdxFt
            If sp >= kSP(j) Then
                Sheets(drnSht).Cells(i, "J") = initK * kFactor(j)
            End If
        Next j
    Next i

    MsgBox "Done!"

End Sub