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
  • Learn & ask questions
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
4
297 Views
Last Modified: 2013-11-13
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
Comment
Question by:sltan32
4 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
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

Open in new window

Regards
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 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

Open in new window

0
 
LVL 24

Accepted Solution

by:
Steve earned 300 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

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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question