Array Problem (Swapping array value based on determined row)

[mbrill]

Hi guys, really appreciate if you can help me solve this problem

So basically, I have one array as the result of other array. Here is the code:

Option Explicit
Sub MakeDynamicArray()
'This VBA aims to populate the data in the sheet into the VBA Array
    
    'This to state the global variable
    Dim Jobs() As Variant
    Dim DueDate() As Date
    Dim ProcessTime() As Variant
    Dim TotalProcessTime As Double
    Dim DDDuration() As Variant
    Dim Starttime As Date
    Dim Penalty() As Double
    
    'This is additional variables to support global variables
    Dim row1 As Long, row2 As Long, row3 As Long
    Dim i As Long, j As Long, x As Long, y As Long, temp As Variant
    Dim TempMinPen As Double
    Dim NumberofRow As Integer
           
    'Activate the sheet that we want to take the value into the array
    Sheet5.Activate
    
    'This to determine the number of rows that will be populated into the array
    row1 = Range("D7", Range("D6").End(xlDown)).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row - 7
    row2 = Range("G7", Range("G6").End(xlDown)).Cells.Count - 1
    row3 = Range("P7", Range("P6").End(xlDown)).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row - 7
    
    'This combine the variable and the row#
    ReDim Jobs(0 To row1)
    ReDim DueDate(0 To row2)
    ReDim ProcessTime(0 To row3)
    
    
    'This to populate all the available rows
    For row1 = LBound(Jobs, 1) To UBound(Jobs, 1)
        Jobs(row1) = Range("D7").Offset(row1).Value
    Next row1
    
    For row2 = LBound(DueDate, 1) To UBound(DueDate, 1)
        DueDate(row2) = Range("G7").Offset(row2).Value
    Next row2
    
    For row3 = LBound(ProcessTime, 1) To UBound(ProcessTime, 1)
        ProcessTime(row3) = Range("P7").Offset(row3).Value
    Next row3
    
   'This to get the sum of all process time and due date duration
   TotalProcessTime = Application.WorksheetFunction.Sum(ProcessTime)
   TempMinPen = 5000
   
   'This to get the due date duration
   Starttime = Range("w19").Value
   ReDim DDDuration(0 To UBound(DueDate, 1))
   ReDim Penalty(0 To UBound(DDDuration, 1))
   
   'This to populate the array of Due date duration
   For i = LBound(DDDuration, 1) To UBound(DDDuration, 1)
        DDDuration(i) = DueDate(i) - Starttime
   Next i
   
   'This to populate the array of Penalty
    For x = UBound(Penalty, 1) To LBound(Penalty, 1) Step -1
        Penalty(x) = TotalProcessTime - DDDuration(x)
     Next x
        
    'This to find the minimum value of penalty and find the row that consist of it
    For i = UBound(Penalty, 1) To LBound(Penalty, 1) Step -1
        For j = i - 1 To LBound(Penalty, 1) Step -1
          If Penalty(j) < TempMinPen Then
            TempMinPen = Penalty(j)
            NumberofRow = j
        
        
            'Swap all array based on numberofrow
            temp = Jobs(i)
            Jobs(i) = Jobs(j)
            Jobs(j) = temp
    
            temp = DDDuration(i)
            DDDuration(i) = DDDuration(j)
            DDDuration(j) = temp
    
            temp = ProcessTime(i)
            ProcessTime(i) = ProcessTime(j)
            ProcessTime(j) = temp
    
            temp = Penalty(i)
            Penalty(i) = Penalty(j)
            Penalty(j) = temp
    
            temp = DueDate(i)
            DueDate(i) = DueDate(j)
            DueDate(j) = temp
        End If
        
    Next j
    
Next I

Endsub

How to exclude the last value in the array out of the calculation of total process time. Moreover, that total process time to be later will be used again to calculate the new penalty of all the jobs exclude the job that has been placed in last row.

This process will loop until all the jobs has been sorted.


Really appreciate for your help!!

Thanks

[snb]

Please study this first:

http://www.snb-vba.eu/VBA_Arrays_en.html