asked on

Excel Custom Fuction unable to constantly show the calcuated value and requires reselection of cells in order to display result

Hi there,

I've created this following custom function to determine the min value based on a selected criteria. The concept is similar to sumif function in Excel.

This is my code below, I'm currently have problem as I always need to reselect the cells in order for the result to be displayed. Excel is unable to store the calcuated value and will always revert to 0 after i go to other sheets to make changes to some of other data.

Can you help to check what is the problem with the code?

Function MinIf(VarMinRange As Range, VarCriteria As Variant, VarMin As Range)
Dim BoolFirstTime As Boolean
Dim IntColumn As Integer

'The purpose of this function is to find out the minimum value that matches a corresponding criteria in a range

BoolFirstTime = True

Set FindColumn = VarMin
IntColumn = FindColumn.Column

For Each VarMinRange In VarMinRange
If VarMinRange = VarCriteria Then
If BoolFirstTime = True Then
BoolFirstTime = False
MinIf = Cells(VarMinRange.Row, IntColumn)
Else
If Cells(VarMinRange.Row, IntColumn) < MinIf Then
MinIf = Cells(VarMinRange.Row, IntColumn)
End If
End If
End If
Next VarMinRange

End Function

Rgonzo1971

Hi,

pls try

Function MinIf(VarMinRange As Range, VarCriteria As Variant, VarMin As Range)
Dim BoolFirstTime As Boolean
Dim IntColumn As Integer

'The purpose of this function is to find out the minimum value that matches a corresponding criteria in a range


BoolFirstTime = True

Set FindColumn = VarMin
IntColumn = FindColumn.Column

For Each VarMinRangeCell In VarMinRange
    If VarMinRangeCell = VarCriteria Then
        If BoolFirstTime = True Then
            BoolFirstTime = False
            MinIf = Cells(VarMinRangeCell.Row, IntColumn)
        Else
            If Cells(VarMinRangeCell.Row, IntColumn) < MinIf Then
                MinIf = Cells(VarMinRangeCell.Row, IntColumn)
            End If
        End If
    End If
Next VarMinRangeCell

End Function

Open in new window

Regards

SOLUTION