# 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
###### Who is Participating?

Commented:
Rory is right about the cells being the issue with recalculation not occurring.

However, why not simply use MIN and IF functions:

Example:
=MIN(IF(D8:D19=F8,E8:E19))
entered using [ctrl]+[shift]+[enter]

See attached workbook for working example
MinIF.xlsx
0

Commented:
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
``````
Regards
0

Commented:
It's your use of Cells that causes the problem - that will always refer to the active sheet in your code. Try this:

``````Function MinIf(VarMinRange As Range, VarCriteria As Variant, VarMin As Range)
Dim BoolFirstTime               As Boolean
Dim n                           As Long
Dim vCrit
Dim vData
'The purpose of this function is to find out the minimum value that matches a corresponding criteria in a range

BoolFirstTime = True

vData = VarMin.Value2
vCrit = VarMinRange.Value

For n = LBound(vCrit, 1) To UBound(vCrit, 1)
If vCrit(n, 1) = VarCriteria Then
If BoolFirstTime = True Then
BoolFirstTime = False
MinIf = vData(n, 1)
Else
If vData(n, 1) < MinIf Then
MinIf = vData(n, 1)
End If
End If
End If
Next n

End Function
``````
0

Author Commented:
Thanks a lot, both solutions provided by Rorya and The Barman works perfectly well..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.