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.

 

[ColinRose]

Horray! Success! Well.. kind of

With your help Sergio I've solved the problem and returned the min value!

Unfortunately it turns out that the unit of measure for the item QTY differs from supplier to supplier...

I'll need to take the values from each run through the loop here, extract the numerical value from the unit of measure and divide the price by the QTY before I get my true MIN value.

However this is a significant victory and I'm delighted

Here is what I ended up doing. I've learnt a heck of a lot over the last few days:

[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 myFormat As String
Dim MyVar As Variant
myFormat = "$#,##0.00;[Red]-$#,##0.00"
myFormat2 = "$#,##0.00_);[Red]($#,##0.00)"
For Each c In Selection
If c.NumberFormat = myFormat Or c.NumberFormat = myFormat2 Then
MyVar = CDbl(c.Value)
Else:
GoTo Skippy
End If
Skippy:
Next c
answer = Application.WorksheetFunction.Min(MyVar)
Windows("Stores Procurement.xlsm").Activate
Range("E6").Select
ActiveCell.Value = answer
/CODE]

Thanks for your help Sergio - it was very much appreciated and, although it melted my head, I couldn't have done this without you!

Kind Regards

Colin