# Minimum value

#### johngio

##### Board Regular
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

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### GlennUK

##### Well-known Member
You can use Application.Min
or the full reference of Application.WorksheetFunction.Min

#### Peter_SSs

##### MrExcel MVP, Moderator
johngio

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

#### johngio

##### Board Regular
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

##### Well-known Member
What are the references of the cells containing those 3 values?

#### johngio

##### Board Regular
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

##### Board Regular
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

##### Board Regular
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

##### Well-known Member
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 )

Replies
1
Views
240
Replies
5
Views
347
Replies
6
Views
359
Replies
6
Views
429
Replies
0
Views
1K

1,171,203
Messages
5,874,332
Members
433,044
Latest member
drewbizzy

### 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.

### Which adblocker are you using?

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

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