Value duplicates in a matrix VBA

[Alex Silva]

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
https://i.stack.imgur.com/Ufn5V.jpg

I execute the code and this is what happens
https://i.stack.imgur.com/QR9tW.jpg

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
https://i.stack.imgur.com/nVOJr.jpg

Change the number orange in row 5 and copy that number to cells("K3")


This is the code i have right now

Code:
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
Code:
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?

 

[Macropod]

Cross-posted at: https://www.ozgrid.com/forum/forum/...cros/1197672-value-duplicates-in-a-matrix-vba

Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html