Find Min Value from given range

peerogel

Board Regular
Joined
Jan 25, 2011
Messages
108
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
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
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 25, 2011
Messages
108
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
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 25, 2011
Messages
108

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 25, 2011
Messages
108
Thanks Rick. That is amazing 100 down to one. Than you Peter, it works great.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,682
Messages
5,524,257
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top