Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2013-11-12
Medium Priority
318 Views
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
0
Question by:sltan32
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 52

Expert Comment

ID: 39643817
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

LVL 85

Assisted Solution

Rory Archibald earned 800 total points
ID: 39644122
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

LVL 24

Accepted Solution

Steve earned 1200 total points
ID: 39644617
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

Author Closing Comment

ID: 39645528
Thanks a lot, both solutions provided by Rorya and The Barman works perfectly well..
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month9 days, 7 hours left to enroll