Macro creation

[PoloArtist]

Hello everyone,
I am new on the forum, I need your help.
I have a huge matrix of numbers. I joined the table in attachment.
I would like a macro to go through this matrix, find the minimum in this matrix.
I would like to put color on the cell where we find this min so we find it easily, and if possible also get the corresponding mean and standard deviation corresponding to this cell which may be the hardest part.
I need the values of the mean and standard deviation of this minimum to derive a function from it.
If you could help me it would be really nice :)
Ask me if you need some more precision

[Kenneth Hobs]

Welcome to the forum!

I don't see a need for VBA though it can be done that way.

For minimum, it looks like you want the full range minimum. For the other two statistics, it looks like you want it for each row.

The minimum for the range would be: =MIN(C5:GU285)
The average or mean for row 6 would be: =AVERAGE(C6:GU6)
The sample standard deviation for row 6 would be: =STDEV.S(C6:GU6)

Use a Conditional Format to set your minimum cell's interior color format. The conditional format would be: Home > Conditional Formatting > Highlight Cells Rules > Equal To... > =$G$2 > OK

[PoloArtist]

Thank for your answer.
What I need is that the program find the minimum value in all this matrix.
And then get this minimun, put it somewhere (this could be made just with the function MIN(). But what I want also is that the program find the position of this minimum value in the matrix and take note of the corresponding mean and standard deviation associated in the matrix.
In the more simple table I sent you, I would like the program to put somewhere:
mini value = 0,103834 (because this is the minimum value of the matrix)
mean=1,6
Standard deviation=2,1 ( mean and standard deviation associated to this minimum value)

Is it more clear now ?
Thank very much for your help :)

[enrand22]

i attach the file with a vba example. hope you find it useful.... for the color in the cells where is the minimun refer to conditional formatting where i write this formula

=if(c6=min($c$6:$h$15),true,false)

and copy formats to the whole matrix and there you are.

[Kenneth Hobs]

Enran22's solution works fine. I would just add an Exit Sub in his If clause.
Sub PoloArtist()
Dim data1 As Double
Dim bcell As Range

data1 = Application.WorksheetFunction.Min(Range("c6:h15"))

For Each bcell In Range("c6:h15")
If bcell.Value = data1 Then
Range("H3").Value = Cells(5, bcell.Column).Value
Range("I3").Value = Cells(bcell.Row, 2).Value
Exit Sub
End If
Next bcell
End Sub

You have many digits so standard formulas would not suffice.

This method is similar and looks more complicated but uses a find method which may be faster if your minimum is towards the end of a large matrix.
Sub MeanStanDev()
Dim f As Range
Set f = FoundRanges(Range("C6:H15"), Range("G3").Value2)
If f Is Nothing Then Exit Sub
Range("G1").Value2 = Cells(5, f.Column).Value2 'mean
Range("G2").Value2 = Cells(f.Row, "B").Value2 'Stan Dev
End Sub

Function FoundRanges(fRange As Range, fStr As Variant) As Range
Dim objFind As Range
Dim rFound As Range, FirstAddress As String

With fRange
Set objFind = .Find(what:=fStr, After:=fRange.Cells((fRange.Rows.Count), fRange.Columns.Count), _
LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True)
If Not objFind Is Nothing Then
Set rFound = objFind
FirstAddress = objFind.Address
Do
Set objFind = .FindNext(objFind)
If Not objFind Is Nothing Then Set rFound = Union(objFind, rFound)
Loop While Not objFind Is Nothing And objFind.Address <> FirstAddress
End If
End With
Set FoundRanges = rFound
End Function

[PoloArtist]

Thank you for your answer both of you. The solution of Enrand work fine it seems. I would like to make your solution to work Kenneth Hob but with my inexistante skills I didn't manage. I will try again to see if I figure out something.

[Tom Jones]

... for the color in the cells where is the minimun refer to conditional formatting where i write this formula

=if(c6=min($c$6:$h$15),true,false)

and copy formats to the whole matrix and there you are.

@enrand22

you can use in conditional formatting only:

=c6=min($c$6:$h$15)

[Kenneth Hobs]

You might want to save my find routine. It will return all the cell ranges that match which I find handy sometimes.

I have attached the example that used my method. I expanded some columns and fixed some digits to show the match.

[enrand22]

Thanks Tom Jones, i test it and you are right!!!

and it is good that is solved, to rome there are several ways