Consulting

Results 1 to 12 of 12

Thread: Excel VBA with Advanced Filter Error messages

  1. #1

    Excel VBA with Advanced Filter Error messages

    I have code in VBA that uses an Advanced filter to fill multiple tables. It worked without any errors before. I upgraded from Office 2013 to Office 365 and suddenly I get error messages on the code...
    The Advanced filter runs, fills, and filters the tables correctly but I still get an error message that pops up "Excel ran out of resources while attempting to calculate one or more formulas. As a result these formulas cannot be evaluated". If I press okay another error shows "Run-time Error 1004: Advanced Filter method of range class failed". The file contains no formulas, 30500 rows, 37 columns.
    EDIT (added tried solutions)
    I've searched for solutions online and tried a couple but none have worked so far. I've tried the following:

    • Closing down all Excel applications and run macro again
    • closing down all application and only running Excel
    • Reduce the core processors for Excel from 4 to 2
    • Turn off the Multi-threaded calculation in Advanced options
    • Remove all .COM add-ins
    • Reduce the file to only use 100 rows and not the full file, tried 50 as well
    • Tried manually using the advanced filter, which doesn't work anymore, I get the same error messages.
    • I checked all the On Error Resume Next code. They are all enclosed and end with GoTo 0
    • Tried removing filters on the extract datasheet and copy to range sheet
    • Tried activating the sheet where I'm copying to first before running the filter
    • Tried changing the filter criteria to a set range and not whole columns Set CorpOrdCompCrit = .Range("$A$8:$F$10")

    I use advanced filter about 20 times in the code, so it's a bit of a problem. One section of the code looks like this (all 20 sections for the advanced filter are structured the same but with different filter criteria, sometimes just 1 filter):

    'Create Filter Criteria ranges
    With MainWB.Worksheets.Add
        .Name = "FltrCrit"
        Dim FltrCrit As Worksheet
        Set FltrCrit = MainWB.Worksheets("FltrCrit")
    End With
    With FltrCrit
        Dim CorpOrdCompCrit As Range
        Dim myLastColumn As Long
        'Create Corporate Order Compliance Filter Criteria Range
        .Cells(7, "A") = "Corp Order Comp"
        .Cells(8, "A") = "MS"
        .Cells(9, "A") = "=4"
        .Cells(10, "A") = "=4"
        .Cells(8, "B") = "SOH"
        .Cells(9, "B") = "=0"
        .Cells(10, "B") = "=0"
        .Cells(8, "C") = "On Order"
        .Cells(9, "C") = "=0"
        .Cells(10, "C") = "=0"
        .Cells(8, "D") = "RP Type"
        .Cells(9, "D") = "Roster"
        .Cells(10, "D") = "Roster"
        .Cells(8, "E") = "Format"
        .Cells(9, "E") = "Corporate"
        .Cells(10, "E") = "Hyper"
        .Cells(8, "F") = "Region"
        .Cells(9, "F") = VRegion
        .Cells(10, "F") = VRegion
        'get last column, set range name
        With .Cells
            myLastColumn = .Find(What:="*", After:=.Cells(8), LookIn:=xlFormulas, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            Set CorpOrdCompCrit = .Range(.Cells(8, "A:A"), .Cells(10, myLastColumn))
        End With
        'CORPORATE ORDER COMPLIANCE
        Dim tblFiltered As ListObject
        Dim copyToRng As Range, SDCRange As Range
        Set tblFiltered = wb.Worksheets("Corporate Order Compliance").ListObjects("Table_Corporate_Order_Compliance3")
        tblFiltered.AutoFilter.ShowAllData
        Set SDCRange = wsSDC.ListObjects("Table_SDCdata").Range
        Set copyToRng = tblFiltered.HeaderRowRange
        'Use Advanced Filter
        SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CorpOrdCompCrit, CopyToRange:=copyToRng, Unique:=False
    The bottom part is where the error pop's up, on this line for the advanced filter.

    'Use Advanced Filter
    SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CorpOrdCompCrit, CopyToRange:=copyToRng, Unique:=False
    Does anyone know why this is suddenly happening and how I can fix the problem, please?

    I posted it on StackOverflow and on Excel Forum 2 months ago but no solutions that worked yet, unfortunately.
    Links:
    https://stackoverflow.com/questions/...t-of-resources
    https://www.excelforum.com/excel-pro...resources.html
    Last edited by Aussiebear; 04-16-2023 at 04:26 PM. Reason: Adjusted the whitespace

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Why don't you post a sample workbook ?

    What is the value of variable Vregion ?

    Why are columns B to D empty in Row 7 ?

    In a criteria range the column'names' should match those of the to be filtered range.
    The criteria range will be automatically given a name after mentioning it in the advancedfilter mathod.
    No need to name it yourself.

    Do not use spaces in a column'name'.

    In principle this code suffices:

    Sub M_snb()
      With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText Join(Array(Join(Array("MS", "SOH", "Order", "RP", "Format", "Region"), vbTab), Join(Array("4", 0, 0, "Roster", "Corporate", VRegion), vbTab), Join(Array("", "", "", "", "Hyper", ""), vbTab)), vbCr)
        .PutInClipboard
      End With
    
      with Sheet1
        .Paste .Cells(100, 1)
        .ListObjects(1).Range.AdvancedFilter 2, .Cells(100, 1).CurrentRegion, sheet2.Cells(1)
        .Cells(100, 1).CurrentRegion.ClearContents
      end with
    End Sub
    Last edited by snb; 08-25-2020 at 06:29 AM.

  3. #3
    Please see attached sample workbooks for raw data and template, with sensitive information removed. I made a small sample size.

    Vregion is specified earlier in the code from a combox, for this instance "Western Cape"

    I use the first row (In this case row 7) just to put a heading above the filter criteria, this is mostly for myself, because as mentioned in my post, there is about 20 different similar cases, so 20 different filter criteria cases. Basically I have a raw data file and the different data needs to be copied with certain criteria and headings to different reports in another workbook.

    I've tried using the advanced filter manually and still gives the same errors but also still copies that data correctly...
    To try and narrow down the problems I did a couple of tests. When I moved all the headings that it needs to copy to, to the same worksheet and workbook as where the raw data is, there were no error messages. If this means anything?
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I see no problem.
    Activate the 'Result' sheet; run the macro in F8-mode.
    Attached Files Attached Files

  5. #5
    Thanks snb, I'll change one of my code sections to match yours and see if it works if I add my variables.

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Formula in cell R9 of the Corporate Order Compliance sheet; remove it. Try again.
    The table seems not to adjust its size according to the results of the Advanced Filter.
    You could get the VBA to remove the formula, adjust the table size to fit and reinstate the formula, or to calculate the value and put it there as a plain value. Whichever, you need to realise that the table doesn't re-adjust its size automatically.
    Last edited by p45cal; 08-25-2020 at 11:12 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7

    Keep the data even after the date change

    Hello,

    Sorry if posting my question here , but actually I m new and i did not know where i can post it.

    i have 2 sheets one is for the data called employee cost which is having a calculation of employees EOS and the other sheet is a report sheet quarterly basis retrieve the date from the first sheet based on formula below as below

    Sample of quarterly report

    DATE EOS LEAVE TOTAL
    26-Aug-20 1,797,331.23 653,240.56 2,450,571.79
    30-Jun-20 - - -
    30-Sep-20 - - -
    31-Dec-20 - - -

    the formula in column EOS is =IF(TODAY()=C4(date first cell),'EMPLOYEE COST 2020'!$AF$128,0)

    as you notice the criteria based on date means when the condition is true the figures will shows up, but next day it will be gone because the date changed( Today()), therefore I need to keep the data showed in that specific date and not change by date changes.

    thanks in advance

    kal
    Last edited by Aussiebear; 04-16-2023 at 04:27 PM. Reason: Adjusted the font size

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    @khalidasaada --

    Welcome to the forum. Please take a minute to read the FAQs in the link in my signature

    It's better to start your own thread -- use the [+Post New Thread] top left side


    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    thank you for your reply but actually i cant find this option no option shows for new post.jpg

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Click 'Excel' and enter the Excel forum first, and the button should be there
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    VBAX Newbie
    Joined
    Mar 2023
    Posts
    1
    Location
    I have solved my issue by correcting the Wrong Formulas. here

  12. #12
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Quote Originally Posted by Sofiya2k View Post
    I have solved my issue by correcting the Wrong Formulas. here
    Welcome to the VBAX forum Sofiya2k. What a strange response for your very first post?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Tags for this Thread

Posting Permissions

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