+ Reply to Thread
Results 1 to 7 of 7

Moving nested IF/AND from in-cell to VBA

  1. #1
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Moving nested IF/AND from in-cell to VBA

    Hi,

    I have a large table with thousands of rows. I'm using a specific nested formula in Column N:N and find that the sheet has become very slow.

    Is it possible to convert the following into VBA for the entire table column, removing the in-cell formula all together?

    Please Login or Register  to view this content.

    Many thanks!

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,605

    Re: Moving nested IF/AND from in-cell to VBA

    Hi,

    First - question:
    Can data change in any of rows, or you just add data at the bottom? If the second is the case, then the cure is simple: Select all (may be but few last) rows in the column with the formula, copy them and paste special as values. They will be now walues and will not recalculate every time you add new data.

    Second - comment:
    VBA code in such case probably will not be faster than the formula. One could try improve it's speed by making all calculations on sheet content copied to arrays (may be to one array, as you check the content of only 5 columns F:J), and then result calculated in a result array, and finally whole array of results pasted into the sheet.

    As for wiriting VBA code, It would be much easier if you attach sample workbook.

    Third - shortening proposition:
    As you are testing often logical values, you could use for instance such notation
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    instead of
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Fourth - question again:
    All cases look at H2 value, both tests for LP do not check column G. Does it mean that there could be FALSE or TRUE there? If so, H2 seems to be the first criterion on the list and may be nesting could be done in this manner:
    if(first criterion, if(second one for first = true, ......,....),if(if(second one for first = false, ....., ....))
    So no AND functions, but testing step by step
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Moving nested IF/AND from in-cell to VBA

    Thank you for the amazing reply!

    You have raised great questions.

    For a bit more background; it is a database where I use UIform to populate certain columns, but also use in-cell formula on 2 columns. This column and this formula is comparing 4 columns of TRUE/FALSE.

    Q1: It is populating at the bottom as a database. I'd like to have minimal manual changes though as we share over Onedrive and sync issues are frequent. If it is possible to convert to values automatically I am all aboard.

    Q2:The reason I am asking for a possibility of VBA formula is that when we submit the UIForm with cmd it takes a good 15 seconds before it reloads. It was submitted with no delay before I put the formula in question in. As to arrays, I have to admit I'm not sure what you mean. Are we talking about an array outside of the table these values are located? Sorry if I'm completely off track.
    I'd like to attach a sample workbook but it will take some time to tidy up all the mess I've made of the original so you'll have to give me 12 hours.

    Q3: Today I Learned!

    Q4: I wish it was that simple but the 4 TRUE/FALSE columns can go 1,3,4 or 1,2,4 etc.

  4. #4
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Moving nested IF/AND from in-cell to VBA

    That was some long 12 hours.

    Attached is the sample. book. I found it easiest in the end to just use a random name generator to swap out real names instead of removing too much of the sheet.

    Hope this helps.
    Attached Files Attached Files

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,605

    Re: Moving nested IF/AND from in-cell to VBA

    I think, you can change all cells in column N to use the Worksheet_Change event handler, to react to change in any of the cells in F:J columns, and temporarily restore formula in column N, recalculate it and store result as value

    The code below is inserted in the sheet module (so right-click on the sheetname tab in excel window and select "display code"

    Please Login or Register  to view this content.
    I used the same mechanism for column M and repeating calls to xlookoop

    But I've noted that there are also some potentialy computing power hungry formulas in other sheets (for instance Zone A filtered list), so test whether improvement in sheet Database really improved overall performance of the workbook - may be the cause is not in this sheet..

    Final note - I decided to exclude row 2 from above tasks. It allows keeping there formulas in case some change in the boundary conditions is implemented.
    And in writing temporarily a formula into M or N column I used two approaches - one is copying formula from row 2 and second: just write it directly as text. In the second approach all double quotes in formula have to be doubled so instead of formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    cell formula has to be written as:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-01-2021
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Moving nested IF/AND from in-cell to VBA

    Thank you!

    This is amazing! And so clear and informative!

    It did speed up the process a little bit but I agree with you that the Zone A filtered list is on the heavy side. I removed Zone B to E filter list to de-clutter for the purpose of making the sample book. But with the lack of other options, I have to have something for the Zone leaders to look a the data through the browser extension of Excel without overwriting each other and this was it.

    I'm so happy that you had a solution to this issue though. Thank you very much again!

  7. #7
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,605

    Re: Moving nested IF/AND from in-cell to VBA

    Hi,

    Glad to hear that,

    Would be a good idea to mark thread as Solved. Above the first post you can see thread tools and between them you'll find mark as solved.

    It would be lovely if you colud also "add me some reputation" by clicking a small star icon in lowerleft corner of our/my post(s). (and confirming in pop-up/dialog window that "you agree").


    As for the other elements which slow down the workbook, I think it would be a good idea to start a new thread about them, Otherwise probably nobody else would find it (using this forum Find option or public search engines).

+ 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: 1
    Last Post: 03-02-2021, 10:50 PM
  2. [SOLVED] Nested VLOOKUPS with a searched, moving range
    By OlYeller21 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2019, 11:33 AM
  3. Replies: 2
    Last Post: 01-05-2019, 03:33 AM
  4. [SOLVED] Nested for each loops, moving to next cell on both simultaneously
    By cs454 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2014, 10:48 AM
  5. Replies: 0
    Last Post: 05-21-2012, 06:59 AM
  6. Copying moving range based on moving cell reff.
    By Varmark in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-19-2011, 04:47 AM
  7. For each loop nested; getting stuck on one cell in first iteration of nested loop
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-22-2009, 11:54 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