+ Reply to Thread
Results 1 to 10 of 10

Array function not returning array (or allowing step thru)

  1. #1
    Registered User
    Join Date
    08-25-2016
    Location
    USA
    MS-Off Ver
    14.0.7172.5000 (32-bit)
    Posts
    96

    Array function not returning array (or allowing step thru)

    I'm not getting the expected output, which is populating a 3x3 spreadsheet block with 0's or 1's depending on whether entries in the selected range (3x3) are divisible by n or not.

    I also can't step into this... I get a beep whenever I try hitting F8 in Developer mode.

    Please Login or Register  to view this content.
    I've attached a screenshot of my spreadsheet.

    Any thoughts as to what's wrong?
    Attached Images Attached Images
    Last edited by Mark43; 09-20-2019 at 01:30 PM. Reason: Nonspecific title

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: What's the problem here?

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    08-25-2016
    Location
    USA
    MS-Off Ver
    14.0.7172.5000 (32-bit)
    Posts
    96

    Re: Array function not returning array (or allowing step thru)

    I've updated the thread title.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Array function not returning array (or allowing step thru)

    Quote Originally Posted by Mark43 View Post
    Any thoughts as to what's wrong?
    Not really as it works fine for me.
    Did you select Z22:AB24 then enter the formula?

    To step through the code, either put the cursor on this line
    Please Login or Register  to view this content.
    & then press F9 (This will set a breakpoint) & then recalculate the function. Or put the stop before that line & then recalc.
    Both options will stop the code & you can then use F8

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Array function not returning array (or allowing step thru)

    Quote Originally Posted by Mark43 View Post
    I've updated the thread title.
    Cheers! I can't help you as I'm feeble with VBA... or maybe even worse than that!

  6. #6
    Registered User
    Join Date
    08-25-2016
    Location
    USA
    MS-Off Ver
    14.0.7172.5000 (32-bit)
    Posts
    96

    Re: Array function not returning array (or allowing step thru)

    Quote Originally Posted by Fluff13 View Post
    Not really as it works fine for me.
    Did you select Z22:AB24 then enter the formula?

    To step through the code, either put the cursor on this line
    Please Login or Register  to view this content.
    & then press F9 (This will set a breakpoint) & then recalculate the function. Or put the stop before that line & then recalc.
    Both options will stop the code & you can then use F8
    I got it to work using CTRL-Shift-Enter after entering the function call in the cell. That also caused the yellow arrow to appear with a breakpoint set. Thanks!

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Array function not returning array (or allowing step thru)

    Judging from your image I thought you had done that as it shows the {}, but glad it's sorted & thanks for the feedback

  8. #8
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Array function not returning array (or allowing step thru)

    Just out of curiosity, why are you doing this with an array function used in an array formula? Wouldn't it be easier to simply use a UDF that tests the one cell it is to process and then copy that formula across and down? This is the function I am thinking of...
    Please Login or Register  to view this content.
    For your posted example, you would put this formula in cell Z22 and copy it across to AB22 and copy Z22:AB22 down to Row 24...

    =DivisibleByN(Z17,3)
    Last edited by Rick Rothstein; 09-20-2019 at 03:19 PM.

  9. #9
    Registered User
    Join Date
    08-25-2016
    Location
    USA
    MS-Off Ver
    14.0.7172.5000 (32-bit)
    Posts
    96

    Re: Array function not returning array (or allowing step thru)

    Quote Originally Posted by Rick Rothstein View Post
    Just out of curiosity, why are you doing this with an array function used in an array formula? Would it be easier to simply use a UDF that tests the one cell it is to process and then copy that formula across and down? This is the function I am thinking of...
    Please Login or Register  to view this content.
    For your posted example, you would put this formula in cell Z22 and copy it across to AB22 and copy Z22:AB22 down to Row 24...

    =DivisibleByN(Z17,3)
    At this point, I'm just learning by trying some different exercises.

  10. #10
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Array function not returning array (or allowing step thru)

    Quote Originally Posted by Mark43 View Post
    At this point, I'm just learning by trying some different exercises.
    In that case, you might find it interesting that the array-formula approach (what you originally asked for) can be done with a one-liner function (no loops required)...
    Please Login or Register  to view this content.

+ 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. Replies: 6
    Last Post: 07-01-2018, 01:58 PM
  2. [SOLVED] Excel Macro - Copy & Paste (Font) problem & Delete Last Added Rows problem
    By LennartB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2015, 06:58 AM
  3. Replies: 1
    Last Post: 04-01-2015, 02:30 PM
  4. Replies: 6
    Last Post: 10-20-2013, 07:16 PM
  5. input box problem, cell filtering problem, result display while locking sheet
    By croozin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2013, 08:52 AM
  6. Replies: 6
    Last Post: 05-28-2013, 05:08 PM
  7. Replies: 2
    Last Post: 01-22-2013, 07:09 AM

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