# Find Min Value from given range

#### peerogel

##### Board Regular
I am trying to find the minimum value on a given range. However, my range starts with a character and the row is formatted as Text. I worked around it by using the Val function but I was wondering if there is an easier way to find the minimum value. Range C21:C25 are blank in this current range so i gave them a value of 1000. I know my range values are less than 1000.

Code:
``````Sub Macro2()
'
' Macro1 Macro
'

'
Dim Rng As Range
Dim X1 As Double, X2, X3, X4, X5, X6, X7, X8, X9, X10, X11, X12, X13, X14, X15

X1 = Right(Range("C11").Value, 2)
If X1 = NullString Then
X1 = "1000"
Else
Val (X1)
End If
X2 = Right(Range("C12").Value, 2)
If X2 = NullString Then
X2 = "1000"
Else
Val (X2)
End If
X3 = Right(Range("C13").Value, 2)
If X3 = NullString Then
X3 = "1000"
Else
Val (X3)
End If
X4 = Right(Range("C14").Value, 2)
If X4 = NullString Then
X4 = "1000"
Else
Val (X4)
End If
X5 = Right(Range("C15").Value, 2)
If X5 = NullString Then
X5 = "1000"
Else
Val (X5)
End If
X6 = Right(Range("C16").Value, 2)
If X6 = NullString Then
X6 = "1000"
Else
Val (X6)
End If
X7 = Right(Range("C17").Value, 2)
If X7 = NullString Then
X7 = "1000"
Else
Val (X7)
End If
X8 = Right(Range("C18").Value, 2)
If X8 = NullString Then
X8 = "1000"
Else
Val (X8)
End If
X9 = Right(Range("C19").Value, 2)
If X9 = NullString Then
X9 = "1000"
Else
Val (X9)
End If
X10 = Right(Range("C20").Value, 2)
If X10 = NullString Then
X10 = "1000"
Else
Val (X10)
End If
X11 = Right(Range("C21").Value, 2)
If X11 = NullString Then
X11 = "1000"
Else
Val (X11)
End If
X12 = Right(Range("C22").Value, 2)
If X12 = NullString Then
X12 = "1000"
Else
Val (X12)
End If
X13 = Right(Range("C23").Value, 2)
If X13 = NullString Then
X13 = "1000"
Else
Val (X13)
End If
X14 = Right(Range("C24").Value, 2)
If X14 = NullString Then
X14 = "1000"
Else
Val (X14)
End If
X15 = Right(Range("C25").Value, 2)
If X15 = NullString Then
X15 = "1000"
Else
Val (X15)
End If
On Error Resume Next
dblMin = Application.WorksheetFunction.Min(X1, X2, X3, X4, X5, X6, X7, X8, X9, X10, X11, X12, X13, X14, X15)
MsgBox dblMin

End Sub``````

Thanks for your time and help.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Peter_SSs

##### MrExcel MVP, Moderator
Do you need to be using vba? Could you just use a worksheet formula? From your code it appears that all the numbers must be 2 digits, is that correct?
Possibly this?

=AGGREGATE(15,6,RIGHT(C11:C25,2)/(LEN(C11:C25)>0),1)

#### peerogel

##### Board Regular
I would need it in vba. The minimum value would not go in a cell, I am using it as part of an "If" statement. If condition is met > find the minimum value in the range >Then do something with offset of the minimum value.

#### Peter_SSs

##### MrExcel MVP, Moderator
I would need it in vba. The minimum value would not go in a cell, I am using it as part of an "If" statement. If condition is met > find the minimum value in the range >Then do something with offset of the minimum value.
You can try the code below to find the minimum. However, in relation to the blue text, have you considered ..
a) is it possible
b) what to do
.. if the minimum value occurs multiple times in the range?

Code:
``````Sub CalcMin()
Dim rngCell As Range
Dim dblMin As Double
Dim vVal As Variant

dblMin = 1000
For Each rngCell In Range("C11:C25")
vVal = Right(rngCell.Value, 2)
If IsNumeric(vVal) Then
If Val(vVal) < dblMin Then dblMin = Val(vVal)
End If
Next rngCell
MsgBox IIf(dblMin = 1000, "No numbers", dblMin)
End Sub``````

#### peerogel

##### Board Regular

Thanks, works great. I'll see if I can make it work for what I'm trying to do.

#### Rick Rothstein

##### MrExcel MVP
Since your range is fixed, I think this macro may also work...
Code:
``````Sub CalcMin()
MsgBox [IF(COUNT(C11:C25),MIN(C11:C25),"No numbers")]
End Sub``````

#### Peter_SSs

##### MrExcel MVP, Moderator

I think this macro may also work...
Rick, I think that you might have missed the following and/or not looked at the OP's code, which was stated as working.
However, my range starts with a character and the row is formatted as Text.
@peerogel
I wrote my earlier code to more closely match the approach you were taking, but a vba adaptation of my earlier formula suggestion would be
Code:
``````Sub CalcMin_v2()
MsgBox Evaluate("aggregate(15,6,right(C11:C25,2)+0,1)")
End Sub``````

Last edited:

#### peerogel

##### Board Regular
Thanks Rick. That is amazing 100 down to one. Than you Peter, it works great.

Last edited:

Replies
1
Views
61
Replies
9
Views
537
Replies
4
Views
104
Replies
3
Views
291
Replies
9
Views
487