Check if all cells between first Min.value and last Min.value in range, are equal.

[soidog]

Hi all,
I have a problem defining the range between first and last Min.value i range("B189:B288").

Sub FindMidVal()


Dim FRowMin     As Double
Dim LRowMin     As Double
Dim AVal        As Double
Dim SearchRange As range
Dim MinValue    As Double
Dim MinRow      As Integer


'define the search range, in this case it is range("B189:B288")
Set SearchRange = range("B189:B288")
'--------------------------------------------------------------------------------------------------------------
'Check if all cells in range have the same value
If WorksheetFunction.CountIf(range("B189:B288"), range("B189")) _
    = WorksheetFunction.CountA(range("B189:B288")) Then
        MsgBox "All values are equal!"
        Exit Sub
End If
'--------------------------------------------------------------------------------------------------------------
'get the Minimum value in the range
MinValue = Application.Min(SearchRange)
'--------------------------------------------------------------------------------------------------------------
'determine where first MinRow is located (if you want all occurances, use VBA find)
MinRow = Application.Match(MinValue, SearchRange, 0)
'--------------------------------------------------------------------------------------------------------------
'One or more "Min.Value"
If Application.CountIf(SearchRange, MinValue) = 1 Then
    range("B184") = SearchRange(MinRow, 0).Value    'This is an offset to col."A"
Else
    'First occurance of "Min.Value" in range
    FRowMin = range("B188:B288").Find(what:=MinValue, after:=range("B188")).Offset(, -1)
    'Last occurance of "Min.Value" in range
    LRowMin = range("B188:B288").Find(what:=MinValue, after:=range("B188"), searchdirection:=xlPrevious).Offset(, -1)


        'Get average value from Col."A"
        AVal = (FRowMin + LRowMin) / 2
        range("B184") = AVal
End If
'--------------------------------------------------------------------------------------------------------------


Dim LMinRow
LMinRow = Columns("B").Cells.Find(MinValue, searchorder:=xlByRows, searchdirection:=xlPrevious).Row


If "all cells between first Min.value and last Min.value in range, are not equal" Then
    MsgBox "All values in Min.value range are NOT equal!"
    Exit Sub
End If
End Sub

I hope this question makes sense. If not, please ask for clarification.
Any help will be greatly appreciated.
Thanks!

 

[soidog]

An example:


Each cell in range("B202:B210") have value 5.
Range("B216") have value 5.
All other cells in range("B189:B288") have value 10.


First Min.value and last Min.value are dynamic.
How to define range (in this example range = ("B202:B216")), with first Min.value and last Min.value?


If "all cells between first Min.value and last Min.value, are not equal" Then
MsgBox "All values in Min.value range are NOT equal!"
Exit Sub
End If


Thanks in advance.

 

[soidog]

'It works hard coded (with my example above) and it looks like this:

If WorksheetFunction.CountIf(range("B202:B216"), range("B202")) _
    = WorksheetFunction.CountA(range("B202:B216")) Then
Else
    MsgBox "All values in Min.value range are NOT equal!"
    Exit Sub
End If

But that isn't going to work because Min.values are dynamic.
Any help would be greatly appreciated.