mbrill
09-10-2018, 10:33 PM
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
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