Minimum value

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
You can use Application.Min
or the full reference of Application.WorksheetFunction.Min
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,437
Office Version
  1. 365
Platform
  1. Windows
johngio

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

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

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

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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
Joined
Jan 28, 2005
Messages
174

ADVERTISEMENT

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
Joined
Jan 28, 2005
Messages
174
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
Joined
Jul 8, 2002
Messages
11,547
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 )
 

Forum statistics

Threads
1,136,508
Messages
5,676,270
Members
419,617
Latest member
Shane50GT

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