+ Reply to Thread
Results 1 to 5 of 5

How to write to multiple cells from a single function call

  1. #1
    Registered User
    Join Date
    08-06-2013
    Location
    the boonies
    MS-Off Ver
    Excel 2007
    Posts
    46

    Question How to write to multiple cells from a single function call

    Currently my UDF WName()outputs a result in each spreadsheet cell that it was called from.

    Now I am focused on developing the last few lines in order to output a second variable ("MinDist") four cells to the right of each UDF call in the spreadsheet.
    I'm trying to do this via ActiveCell.Offset., but not getting the result I need. Here's a snipit:
    Please Login or Register  to view this content.
    Full spreadsheet and VBA code attached.

    Note that for debugging, I'm just looking now at sheet "A", cell "A14"

    Any suggestions?

    Thanks!
    g
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    You can't use a UDF to alter, eg put values in, cells on a worksheet.
    If posting code please use code tags, see here.

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to write to multiple cells from a single function call

    a function can only return a value to its own cell*. you can have your function return an array and then use index to retrieve whichever value you want or array enter the function call into multiple cells

    *there are ways around this but you should not use them!
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: How to write to multiple cells from a single function call

    Correct, that sort of thing is something that a UDF cannot do.

    In order to return multiple values into multiple cells, you will need to code your UDF to be an array function (Similar to LINEST, FREQUENCY, TRANSPOSE). When I do it, this looks something like:

    Please Login or Register  to view this content.
    called from two adjacent cells as =myarrayfunction(arg1,arg2,...) and entered as an array function (ctrl-shift-enter). If you need to review how to enter array functions, you can call up the help files for any of the built in array function I've mentioned and there should be some discussion on how to enter array functions.

    One problem with the problem as you've presented it is that the cells you want to return to are not adjacent. An array function like this will usually be expected to return the results to adjacent cell.

    If result2 must be returned 4 cells over from result1, you can use the INDEX() function to return each result individually
    =INDEX(myarrayfunction(arg1,...),1) and =INDEX(myarrayfunction(arg1,...),2). However, this means two function calls instead of one. If I remember from your other thread, you want to call this function thousands of times already, doubling the number of function calls could increase computation time. I think you will be better off if you can structure your spreadsheet so that the results of the array function are returned to adjacent cells. If you really need to separate the results, you can have the UDF return in an out of the way place on the spreadsheet, then link those results to the desired output cells.
    Last edited by MrShorty; 08-14-2013 at 10:48 AM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    08-06-2013
    Location
    the boonies
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: How to write to multiple cells from a single function call

    Sage advice all around. Thanks especially to Mr. Shorty for the detailed explanation/suggestion.
    I elected to go for the 2x computation time. It'll get the job done and give me time to go get a cup of coffee.

    Updated final code attached.

    SOLVED.

    Cheers to Excel Forum for leading me by the hand through my first VBA code. This website is a great resource.
    Attached Files Attached Files

+ 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. [SOLVED] How do i write to cells in a function?
    By Turtleman10 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-28-2012, 11:01 AM
  2. [SOLVED] MsgBox to call up multiple cells
    By dpcp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-23-2012, 11:35 AM
  3. Replies: 11
    Last Post: 08-22-2011, 12:40 PM
  4. Call in the Calvary...How to get multiple column data in single column
    By raj0070 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2011, 10:26 AM
  5. How to write macro for copy data from multiple sheet to a single sheet.
    By Santoshmoni in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-16-2011, 04:39 AM

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