Value duplicates in a matrix VBA

Alex Silva

New Member
Joined
Nov 17, 2017
Messages
3
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?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,650
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top