Application.min doesn't recognize negative numbers or percents

[talt0612]

Hi all-

I'm trying to find/select the cell containing the minimum value in a range and I have two problems. First, my program does not recognize negative values. In other words, it will select a cell containing a zero value instead of selecting a cell containing a negative value. Second, if I change the number formats to percentages, I don't get any results.

So in a nutshell, I'm trying to find the min percentage in a range and select that cell. Below is what I've tried. Any thoughts/comments are appreciated.

S13:S20 contains the following values:
<TABLE style="WIDTH: 44pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=58><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 44pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 width=58 align=right>-1.54%
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>0.40%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>0.11%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>3.76%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>0.82%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>-1.15%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>-0.65%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 align=right>-1.75%</TD></TR></TBODY></TABLE>

Sub min()
Dim MinVal
Dim RowMin
Dim WorkRange As Range
Dim rngFnd As Range
Set WorkRange = Range("S13:S20")
MinVal = Application.min(WorkRange)
If Not IsError(MinVal) Then
RowMin = Application.Match(Maxmin, WorkRange, 0)

If Not IsError(RowMin) Then
WorkRange.Cells(RowMin).Select
End If
End If

End Sub

 

[pgc01]

Hi

It doesn't compile. What is MaxMin?

 

[talt0612]

Ah yes, of course 'maxmin' should be MinVal. Apologies for wasting everyone's time.

 

[pgc01]

I'm glad your problem is solved.

Remark: You can avoid this type of errors.

At the beginning of each module write "Option Explicit", or better yet, in the tools->options->Editor check "Require variables declaration". This means that the vb editor inserts the statement automatically when you insert a module.

In this case I just posted your code into the vb editor and it told me immediately that the variable declaration was missing.

 

[talt0612]

PGC
Thank you for the tip. That is very helpful.
Regards,
T