+ Reply to Thread
Results 1 to 13 of 13

Using .FillUp and Array Formula - Unsuccessfully

  1. #1
    Registered User
    Join Date
    05-07-2019
    Location
    Midwest
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Using .FillUp and Array Formula - Unsuccessfully

    ***PLEASE DISREGARD THIS POST, TWO POSTS DOWN HAS BEEN RESTATED***

    I am attempting to use .FillUp to recover formatting and blank cells in my worksheet. However, this process is corrupting my array formula. After I .FillUp, I must then go back to my array formula, ctrl+alt+Enter and then float down.




    Also posted on mr excel five days ago with no replies. (no link, new member here)
    Last edited by AliGW; 05-08-2019 at 04:57 PM. Reason: Correctly tagged as solved.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using .FillUp and Array Formula - Unsuccessfully

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-07-2019
    Location
    Midwest
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Re: Using .FillUp and Array Formula - Unsuccessfully

    This should be significantly scrubbed, and I believe the core functionalities are intact.

    The column we are looking at is the Shortages column. This is the formula which is not sticking. After cleaning, if I click in the Q column, then hit Ctrl+Shift+Enter, the formula resets to array and works fine. But if I leave it, it is broken.

    To button control, you would go to the dashboard and click on "Clean" at the top left. This wipes the Working_Dispatch, which is the problem area. It also wipes the job_dispatch, and so I have a 'sample' of data in "Job_Dispatch Copy to Yellow" which will allow you to copy data over to check. Copy data only, entire worksheet and it should be fine.

    Otherwise, you'll find all of my code relevant to the cleaning on ClearSheet. Some of the stuff in there is pretty hackish. Several years of developing this workbook, different software, and a 20 year old class in VBA has rendered me this far.

    Thanks for taking a look,

    Jon
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using .FillUp and Array Formula - Unsuccessfully

    I'll look at this tomorrow (UK time) ... 10pm right now.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Using .FillUp and Array Formula - Unsuccessfully

    Jon

    How can we replicate the problem?
    If posting code please use code tags, see here.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using .FillUp and Array Formula - Unsuccessfully

    Never used .FillUp but I would drop the formula into the top data row the copy and paste the cell to the rest of the column. Then I’d use the .FillUp to restore the formatting.

    That said, I’ll test the theory when I get the chance. Unless Norie fixes it first

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using .FillUp and Array Formula - Unsuccessfully

    A little confused as I couldn't find the specific code (from post #1) in the VBA Project. And the formula quoted refers to a worksheet that doesn't exist in the sample workbook … ShoReport vs ShortageReport. Also, it was not clear to me which cell, and in which sheet, that formula was meant to go. Still not sure about the use or need for .FillUp.

    However, as I said, this is how I would place the formula in the column:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-07-2019
    Location
    Midwest
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Re: Using .FillUp and Array Formula - Unsuccessfully

    Thanks for the help.

    Post #1 was edited in code. I did not edit the post, figured second post would stand alone. I will edit later today.

    it does state where code is going, here:

    The column we are looking at is the Shortages column. This is the formula which is not sticking. After cleaning, if I click in the Q column, then hit Ctrl+Shift+Enter, the formula resets to array and works fine. But if I leave it, it is broken.
    I can set the code with VBA, I guess, but why? Why does the array not fill up or down with all of the rest of the giblets?

    Again, thanks for help,

    Jon

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using .FillUp and Array Formula - Unsuccessfully

    Post #1 was edited in code. I did not edit the post, figured second post would stand alone. I will edit later today.
    You understand how that might be a little confusing for someone who knows nothing about your workbook, data, code or processes?

    it does state where code is going, here:
    I missed that because I was copying the formula from the first post.

    I can set the code with VBA, I guess, but why? Why does the array not fill up or down with all of the rest of the giblets?
    That, I do not know. There are often things in Excel and VBA that I think should work but don't. Sometimes it's my understanding, sometimes it's a foible or restriction in Excel or VBA, sometimes I've cocked up. It happens. As I said, I don't use .FillUp so I don't know the nuances. That said, it could be that it doesn't like Array Formulae.

    For example, this works:
    Please Login or Register  to view this content.
    But this doesn't:
    Please Login or Register  to view this content.

    This is the edited code to put the formula in Column Q AND use .FillUp to copy the formatting from the bottom row.

    Please Login or Register  to view this content.
    This version also works but, as you can see, I can't just drop the formula into the last row and fill up. I drop it in the last row, copy it into the previous row and FillUp from there. Swings and roundabouts.

    Please Login or Register  to view this content.
    And don't ask me why I have to do it that way … same reason I can't drop it in the top row and copy down from there. It doesn't work. There may/must be a good reason but I don't know what it is.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using .FillUp and Array Formula - Unsuccessfully

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using .FillUp and Array Formula - Unsuccessfully

    Is this resolved now?

  12. #12
    Registered User
    Join Date
    05-07-2019
    Location
    Midwest
    MS-Off Ver
    365 ProPlus
    Posts
    4

    Re: Using .FillUp and Array Formula - Unsuccessfully

    Yes, thank you! I thought I resolved this earlier, but will do so now. I also have some comments regarding your post which seem to have gotten lost. Thank you very much for answering and steppign through. Very strange environment to work in, and I really wish I could figure out the why. I can format, then restate formula, but that is just a very strange behavior, esp what you pointed out.

    Sorry did not get back to this earlier, day has blown up.

    Thanks again,

    Jon

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Using .FillUp and Array Formula - Unsuccessfully

    You're very welcome. Thanks for the rep.

    Glad I could help and think you for the feedback.

+ 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] FIllup empty cells MACRO
    By rz6657 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-16-2016, 03:53 PM
  2. [SOLVED] auto fillup of days/date of month
    By iamhsn in forum Excel General
    Replies: 3
    Last Post: 05-18-2012, 11:05 PM
  3. fillup the Branches productivity
    By migdad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2011, 06:11 AM
  4. [SOLVED] Testing (unsuccessfully) for non-blanks
    By Coach in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-10-2006, 12:19 AM
  5. (Unsuccessfully!) Unhiding Rows in Excel 2003
    By [email protected] in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-24-2006, 03:20 PM
  6. Autofill:already fillup coding
    By Peter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2005, 10:06 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