Minimum value

[johngio]

Hi all,

I am trying to write a line of code (within a macro) to check for the minimum value in 3 columns. I have heard mention of a Min function, but cannot find how to use it.


As a rough idea, this is what I am trying to achieve:

If Cells(8, 31).value = Min(Cells(7, 31).value, Cells(8, 31).value, Cells(9, 31).value) Then

Can someone assist me in the correct formatting of this command?

Cheers

John

 

[GlennUK]

You can use Application.Min
or the full reference of Application.WorksheetFunction.Min

 

[Peter_SSs]

johngio

What about:
If Cells(8,31).Value <= Cells(7,31).Value And Cells(8,31).Value <= Cells(9,31).Value Then

 

[johngio]

I tried both methods. The second one I couldn't get to work but I had a little success with application.min. Here's my test macro:

Sub Minimum()
If Cells(7, 31).Value = Application.Min(Cells(7, 31), Cells(8, 31), Cells(9, 31)) Then
Cells(1, 26).Value = "yay"
Else
Cells(1, 26).Value = "****"
End If
End Sub

The problem is if I enter If Cells(7,31) I get an error, and if I enter Cells(8,31) or Cells (9,31) I get the output yay. I should only get this if I enter Cells(9,31) as the values in these cells are:

71642 19174 18888

Any ideas?

Thanks

John

 

[GlennUK]

What are the references of the cells containing those 3 values?

 

[johngio]

Glenn,

I don't know if I understand what you mean.

The cells in question are G31,H31 and I31 and the values in these cells are:

71642 19174 18888

Cheers

John

 

[johngio]

I managed to get this to work for a set range by doing this:


Sub Min()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:C10")
Cells(1,1) = Application.WorksheetFunction.Min(myRange)

End Sub

Now I try to change this to work for a dynamic range by doing this:

Sub Min()
For iRow = 28 To 50
For iCol = 7 To 9

Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("iRow,7:iRow:9")
Cells(iRow, 1) = Application.WorksheetFunction.Min(myRange)
Next iCol
Next iRow
End Sub

This gives me an error code: I don't think it likes Range("iRow,7:iRow:9")

Does anyone know how I can correct this?

This is in aid of being able to flag the minimum cell from the 3 columns so I can add to it (ideally I want to be able to say If cell (iRow,iCol) = min (iRow,7:iRow9) Then . . .

Any ideas?

Thanks

John

 

[johngio]

Woohoo!

I have got this to work using the following code:

Sub Min()
For iRow = 28 To 50
For iCol = 7 To 9

Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range(Cells(iRow, 7), Cells(iRow, 9))

Cells(iRow, 1) = Application.WorksheetFunction.Min(myRange)
Next iCol
Next iRow
End Sub

 

[GlennUK]

Glenn,

I don't know if I understand what you mean.

The cells in question are G31,H31 and I31 and the values in these cells are:

71642 19174 18888

Cheers

John

Glad you got it working. Yes, that's what I meant. Your code used to have "Cells(7, 31), Cells(8, 31), Cells(9, 31)" in it ... which is referencing a completely different set of cells, which is what I suspected. ( row and column indexes wrong way round )