[h=1]I have this code to get the smallest number that can find and his column in each row of the matrix and write it in cells(column where it was found, row)
This is the matrix at the beginning
I execute the code and this is what happens
The code works as intended he always finds the smallest number in a row, make it orange and copy the right machine.
The problem now is how can i have the program check the column "Total" each time there is more than one smallest number in the same row and make orange and copy the number wicht row has the smallest "Total"
It should do this to the matrix
Change the number orange in row 5 and copy that number to cells("K3")
This is the code i have right now
Dim tarefas, maxcolunaspossiveis, coluna As Long tarefas = 4 maqs = 5 maxcolunaspossiveis = (maqs + 4) + tarefas + 1 Cells(1, maxcolunaspossiveis) = "Total" For i = 2 To tarefas + 1 For q = 2 To maqs + 1 Cells(q, maxcolunaspossiveis) = Application.Sum(Range(Cells(q, maxcolunaspossiveis - tarefas), Cells(q, maxcolunaspossiveis - 1))) Next q menor = WorksheetFunction.Small(Range(Cells(i, 2), Cells(i, maqs + 1)), 1) coluna = Application.Match(menor, Range(Cells(i, 2), Cells(i, maqs + 1)), 0) Cells(i, coluna + 1).Interior.ColorIndex = 45 Cells(coluna + 1, Cells(coluna + 1, maxcolunaspossiveis).End(xlToLeft).Column + 1) = menor Next i For q = 2 To maqs + 1 Cells(q, maxcolunaspossiveis) = Application.Sum(Range(Cells(q, maxcolunaspossiveis - tarefas), Cells(q, maxcolunaspossiveis - 1))) Next q End Sub
I know i can have something like this to get the number of smallest number that exist in that row
But i don't know what to put in the else part
Dim iVal As Integer iVal = Application.WorksheetFunction.CountIf(Range(Cells(i, 2), Cells(i, maqs + 1)), menor) If iVal = 1 Then ............What i currently have ..... Else ............What do i do here?.........
Can someone help me?[/h]
[VBA]vba & excel, matrix, table, algorithm[/VBA]