VBA - select MIN value from range conditionally

[ColinRose]

Hi there,

I'm attempting to return the minimum value from a range I've selected but I'm unsure how I can select the minimum value in the range (only) where the cells (in the range) are formatted thus:

£#,##0.00;[Red]-£#,##0.00

The VBA code I've used is as follows to get the MIN value and this works for my purposes:
Range("E2").Select
Selection.AutoFilter Field:=5, Criteria1:=extvalueB
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5).Select
Set Rng = ActiveCell.Offset(, 1).Resize(, 100)
answer = Application.WorksheetFunction.Min(Rng)

So if the range was like "1,1,£5.00" I'd be hoping to return the £5.00. At the moment I can only get the value 1 as the answer.

Could someone possibly help me out here? I've trawled google looking for something that I can use but with no success thus far.

 

[sergioMabres]

Hi Colin,
What about you select the range and call this

Dim myMin As Double
Dim myFormat As String
myMin = 99999999
myFormat = "[COLOR=#333333]£#,##0.00;[Red]-£#,##0.00[/COLOR]"
For Each c In Selection
If c.NumberFormat = myFormat & c.Value < myMin Then
   myMin = c.Value
End If
Next c

The answer is in myMin
Cheers
Sergio

 

[ColinRose]

Hi Sergio,

That solution looks really good and I want to thank you for getting back to me. I'm having a little trouble with it though.

I've incorporated it but I'm getting the following error:

"Run-time error '13': Type mismatch" on the myMin = c.Value line

I'm thinking this is something to do with the formatting of whatever is in my range (the selection in your code) but that's just a guess really. You got any ideas? (The code looks like this now):

Range("E2").Select
Selection.AutoFilter Field:=5, Criteria1:=extvalueB
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5).Select
Rng = ActiveCell.Offset(, 1).Resize(, 100).Select

'TEST SECTION'
Dim myMin As Double
Dim myFormat As String
myMin = 99999999
myFormat = "£#,##0.00;[Red]-£#,##0.00"
For Each c In Selection
If c.NumberFormat = myFormat & c.Value < myMin Then
myMin = c.Value
End If
Next c
'TEST SECTION'

answer = Application.WorksheetFunction.Min(myMin)

Kind Regards

Colin

 

[sergioMabres]

Hi colling
May be c.Value is not a number, so you have to check if c.Value is a numbre before the line myMin = c.Value
For instance use an If and only asign myMin = c.Value if cValue is a number
Cheers
Sergio

 

[ColinRose]

Hi Sergio,

I amended the code slightly as you said and I'm now getting a numerical result.... £99999999

I think I know why though. I looked a the source cell which I thought was formatted £#,##0.00;[Red]-£#,##0.00 and it turns out it isn't:

Sub WTF()
Dim myFormat As String
Range("F3").Select
myFormat = "£#,##0.00;[Red]-£#,##0.00"
For Each c In Selection
If c.NumberFormat = myFormat Then
MsgBox "Yes it is"
Else:
MsgBox "No it isn't"
End If
Next c
End Sub

Left me the soul crushing message: "no it isn't". Now I need to figure out what the cell is actually formatted and I might get a result. For reference, I changed the original formula to this:

Range("E2").Select
Selection.AutoFilter Field:=5, Criteria1:=extvalueB
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5).Select
ActiveCell.Offset(, 1).Resize(, 100).Select

Dim myMin As Double
Dim myFormat As String
myMin = 99999999
myFormat = "£#,##0.00;[Red]-£#,##0.00"
For Each c In Selection
If c.NumberFormat = myFormat & c.Value < myMin & IsNumeric(c.Value) = True Then
myMin = c.Value
End If
Next c

answer = Application.WorksheetFunction.Min(myMin)

I'll keep the thread updated when I'm actually able to nail this down. I'm off to find out how to determine the cell format!

Kind Regards

Colin

 

[sergioMabres]

If you get 999999999 at the end the function should say: "No numeric value found in range"
Also you could show in the msgbox the value of the cell with: MsgBox "Yes it is with value: '" & c.Value & "'."
Cheers
Sergio

 

[ColinRose]

I used a user-defined function to find out the format... it's actually: $#,##0.00_);[Red]($#,##0.00)

I'm going to amend the code to take this into account and I'll be right back...

 

[sergioMabres]

so no "£#,##0.00;[Red]-£#,##0.00" cells at all, actually you are using "$#,##0.00_);[Red]($#,##0.00)"
so no cells where evaluated, that is why it shows 99999999999

 

[ColinRose]

Yep, I've decided to have a closer look at the sheet I've been sent over and the fields are not even numeric. I'm going to have to find a way to convert each cell with that format into a number as well Google tells me "CInt" might be helpful...

I hate this spreadsheet! I'm calling it a day. I'll return tomorrow for another round. (It's good in a way as I'm a complete VBA novice so I'm learning a lot - the hard way).

 

[ColinRose]

OK I think I'm tying myself in knots here. I cannot for the life of me get this to work. Here is my latest:

Dim myMin As Double
Dim myFormat As String
myMin = 99999999
myFormat = "$#,##0.00;[Red]-$#,##0.00"
For Each c In Selection
 
On Error GoTo Skippy
Dim MyVar As Variant
MyVar = CLng([COLOR=#ff0000]c.Value[/COLOR])    
 
If c.NumberFormat = myFormat & MyVar < myMin & MyVar > 0 Then

    myMin = MyVar
End If
Skippy:
Next c
answer = Application.WorksheetFunction.Min(myMin)

whicdh returns the "type mismatch" error again.

I created a test bit of code to make sure I was doing this right and it appears to work OK (returns the value "26" which is correct):

Sub TESTTING()
On Error GoTo Error
Dim MyVar As Variant
MyVar = CLng(ActiveSheet.Cells(34, 6).Value)
MsgBox MyVar
GoTo Endsub
Error:
    MsgBox "Fail"
Endsub:
End Sub

So I'm wondering what on earth I am doing wrong here? I'm very new to all this but I assume that it's something to do with the c.value I highlighted in red above?

Kind Regards

Colin