+ Reply to Thread
Results 1 to 11 of 11

Help with UDF: Sum the results of a calculation across a pair of cells down entire ranges

  1. #1
    Registered User
    Join Date
    01-07-2016
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    3

    Help with UDF: Sum the results of a calculation across a pair of cells down entire ranges

    I am very new to VBA (this is the first thing I've ever built using it), so I apologize if my terminology is off.
    I tried my hand at building a small handful of UDFs to simplify a somewhat annoying task that some of my colleagues and I regularly have to complete. Each of these UDFs essentially takes a multiple-if statement and simplifies it so that instead of having to type...

    Please Login or Register  to view this content.
    ...someone could just type out...

    Please Login or Register  to view this content.
    The UDFs that I created seem to be working fine. However, I want to be able to take the sum of NEWFUNCTION for a range by entering something like
    Please Login or Register  to view this content.
    I am having trouble figuring out how to code this in VBA properly, and was hoping for some advice. The code for one of my UDFs is below....

    Please Login or Register  to view this content.
    To explain further, here is an example screenshot

    This is just a simplified example, but there can be tens of thousands (or more) rows.

    Currently, the formula populating C2 is
    Please Login or Register  to view this content.
    which is dragged down to C20. It is basically saying, if A is less than B, then A, otherwise B. But there are some intricacies with the way that negative numbers and zero values are treated that are not apparent in the screenshot, which is why the formula appears unnecessarily lengthy.
    The formula in D2 and dragged to D20 is
    Please Login or Register  to view this content.
    ....for which the VBA code is above. You can see in the example screenshot that it ends up with the same result as the pasted if/then formula in C2 and (in row 21) they sum up to the same amount.
    The value in F2 (which is the same as either SUM(C2:C20) or SUM(D2:D20), is what I would like to be achievable by entering something like
    Please Login or Register  to view this content.
    I am basically trying to remove a step in the process where my colleagues and I would have to type
    Please Login or Register  to view this content.
    in Column C, drop it down, and sum it.
    Last edited by frankthomas35; 01-07-2016 at 01:11 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    409

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    Try this - hope it's what you are after...
    Please Login or Register  to view this content.
    Last edited by mgs73; 01-07-2016 at 01:33 AM.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    here is my attempt

    Please Login or Register  to view this content.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    mgs

    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    combined, will give you #value error if there is any zero's in first range

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    mgs

    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    combined, will give you #value error if there is any zero's in first range

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    my previous code seems to fail if the range is only 1 cell
    so i have added more code to counter it

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    Looking at your Original question your first formula reads
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    but lower down it reads
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    there is no minus in the second formula at all

    the 2nd code does not match what you wrote in the "code" format

    is the first or the second one correct?
    Attached Files Attached Files
    Last edited by humdingaling; 01-07-2016 at 02:29 AM. Reason: attached working file

  8. #8
    Registered User
    Join Date
    01-07-2016
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    3

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    My fault. The 1st code is correct. I accidentally copied the formula out of the wrong cell when I posted here. Thanks for catching. Will update.
    Last edited by frankthomas35; 01-07-2016 at 08:43 AM. Reason: Typo

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    in that case no amendment of the UDF is required

    does the UDF supplied do the job you want?

  10. #10
    Registered User
    Join Date
    01-07-2016
    Location
    Chicago
    MS-Off Ver
    2010
    Posts
    3

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    Yeah! It appears to be working fine. And the code is the same as what you posted above?

    I have a few other functions that are somewhat similar in structure (nested if/then statements) that i am going to try to apply the same treatment to using that as a model. Thanks!

  11. #11
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

    you can certainly apply the same framework for different UDF

    if your nested if/then gets more complicated you might want to investigate in using SELECT case instead (which is a benefit of using UDF which you cannot use with just normal formulas)
    http://www.techonthenet.com/excel/formulas/case.php

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How do I pair up numbers in a lottery results list?
    By code_flea in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-05-2014, 11:17 PM
  2. VBA Needed for all possible two-pair combination of cells
    By mailsodia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-29-2012, 06:04 AM
  3. How do I evaluate a pair of cells
    By jmetz in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-29-2012, 02:31 PM
  4. Pair down results based on input
    By pjbassdc in forum Excel General
    Replies: 6
    Last Post: 09-14-2011, 02:35 PM
  5. Count in pair of 5 cells
    By alinpion in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-12-2011, 08:58 AM
  6. Populating a field based on a pair of cells.
    By Dr.Acula in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2010, 03:59 AM
  7. Matching one pair of cells with another
    By Giselle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-19-2006, 08:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1