Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    171
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Want a user function in Excel to add incrementing counter to list of values

    Hi all,

    Excel.

    Is there a user function to add an incrementing counter to a list of values?

    E.g., I want to be able to select an entire row and then apply the function so that the 1st value in the row is incremented by one, the 2nd value in the row is incremented by two, and so on.

    TIA

    -avraham

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,826
    Thanks
    185
    Thanked 704 Times in 642 Posts
    Avraham,

    If the following is what you're looking for:
    Increment Values.PNG
    Here's a some VBA code to accomplish the trick. Just select the desired values and run the macro. Note that in the picture above both rows started with the values in row 1.
    Code:
    Option Explicit
    
    Sub PositionalIncrement()
       
       Dim rngCurCell As Range
       Dim lCntr         As Long
       
       lCntr = 1
       
       For Each rngCurCell In Selection
          rngCurCell.Value = rngCurCell.Value + lCntr
          lCntr = lCntr + 1
       Next rngCurCell
       
    End Sub
    Also note that the increment starts with the first selected cell so if you select C1:G1 the results would be 4, 6, 8,10!
    Last edited by RetiredGeek; 2012-07-26 at 08:51.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    171
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi RG
    Thanks!

    Yes - that's exactly it !

    But isnt there a user function for that - and not a VBA soln?

    I always first ask for a user function.

    Failing that - what are the ways that I can turn your macro into one that looks as if it was "born" with Excel? I.e., like the integrated Excel functions.

    -avraham

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,826
    Thanks
    185
    Thanked 704 Times in 642 Posts
    Avraham,

    The builtin & user defined functions in excel are designed to be placed in a cell and calculate a value. You have a series of cells with values (not formulas) that you want changed not what these Functions are designed to do.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    171
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Is that so ...?

    Just to repeat that, you mean to say that the MS "vision" for all Excel's built-in fucntions are that they work on just *one* cell and not a series of cells...?

    I am very surprised.

    A spread sheet is well - s-p-r-e-a-d o-u-t ... so with that in mind, I would have thought that all the functions should be able to act "spread out" over cells ...

    -avraham

  7. #6
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Ulaan Baatar
    Posts
    507
    Thanks
    1
    Thanked 42 Times in 40 Posts
    All functions deliver a result into one cell, although the calculation may draw on many cells to produce the result.

    If you want to change many cells, you either need to put the appropriate function (user-defined or built-in) into each cell, or write a macro which performs the action you want on each cell in turn.

    In your case you cannot use a user-defined function because . . . how would it know when to run ? If, maybe, when one of the cells has changed . . . then how would it know when to stop ? It could easily get into a continuous loop.

    I expect that you want to increment all the cells in a row at a time of your choosing. The neatest way to do that would be to write the macro and assign it to a button (or any other object, such as a text box). If there is some event which decides when to increment the row then perhaps you can use that to trigger the macro.

    Microsoft doesn't restrict you to only changing one cell, its just that Excel has one set of methods for changing single cells (Functions) and another set of methods for changing many cells (Macros). One of the reasons for this approach is that it is relatively easy to identify a standard set of things users may wish to do with single cells, and produce a standard set of functions. In the case of changing many cells, though, every user-requirement will be different so instead of providing standard functions Excel provides a standard language (VBA) equipped with many tools with which to achieve multi-cell results to each user's individual requirements.

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,826
    Thanks
    185
    Thanked 704 Times in 642 Posts
    Martin,

    Thanks. A much better explaination than mine.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,162
    Thanks
    14
    Thanked 317 Times in 311 Posts
    All functions deliver a result into one cell
    This is a false statement. There are array functions (built-in as well as user-defined) which can return values to multiple cells...

    There are only some subtle distinctions between subroutines and functions. The single biggest is that functions explicitly return values and subroutines do not. But subroutines can be made to appear to do this (as the code here does).

    Steve

  10. #9
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Ulaan Baatar
    Posts
    507
    Thanks
    1
    Thanked 42 Times in 40 Posts
    Yes, I had indeed oversimplified !

  11. #10
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    171
    Thanks
    3
    Thanked 0 Times in 0 Posts
    >> This is a false statement.

    Ok - can you tell me how to do what I want to do by using a predefined User function?

    Or can I assign the macro to a user function?

    -avraham

Posting Permissions

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