Find Min Value from given range

[peerogel]

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.

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.

 

[Peter_SSs]

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]

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]

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?

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]

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

 

[Rick Rothstein]

Since your range is fixed, I think this macro may also work...

Sub CalcMin()
  MsgBox [IF(COUNT(C11:C25),MIN(C11:C25),"No numbers")]
End Sub

 

[Peter_SSs]

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

Sub CalcMin_v2()
 MsgBox Evaluate("aggregate(15,6,right(C11:C25,2)+0,1)")
End Sub

 

[peerogel]

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

 

[Peter_SSs]

Than you Peter, it works great.

You're welcome.