Consulting

Page 5 of 5 FirstFirst ... 3 4 5
Results 81 to 84 of 84

Thread: VBA - Search For Value Across Multiple Worksheets

  1. #81
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    I can see the file.

    Do you expect miracles? Didn't it take two hours on yours to run? I haven't got a Magic Wand!

    Please wait... analysing data.

    0% Complete
    _______________________________________________
    Semper in excretia sumus; solum profundum variat.

  2. #82
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Several reasons that I can see for it being a large file and taking forever to load and calculate

    I never bothered to let it calculate since it takes so l-o--o-n-g


    1. There must be 10s of 1,000s of empty cells that have a formula in them that requires recalculating if any dependent data changes

    2. There must be dozens of completely data-less sheets, filled with formulas

    3. There is a lot of formatting in cells that takes space

    4. Your worksheets go almost 20 years into the future with formulas, but no raw data into that time period

    5. The XLSM format is a compressed ZIP under the hood - Just the main Excel unpacked (so Excel can get to it) is 216 MB, with just the worksheets using 196MB. So I'm guessing that there's a lot of paging going on

    6. Your CalcChain.xml is almost 17 MB unpacked (I think it controls the order of cell calculation).

    7. Some formulas are very complex and seem to require dependent data, forcing a re-calc. Also, many cells have that formula, but the cells that it uses are blank

    See #64

    Capture.JPG

    Capture.JPG


    Capture.JPG

    Capture1.JPG

    Capture.JPG
    Attached Images Attached Images
    Last edited by Paul_Hossler; 05-21-2020 at 08:34 AM.
    ---------------------------------------------------------------------------------------------------------------------

    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

  3. #83
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Well explained Paul H, but I think you are being a bit kind there! I can only see two ways of dealing with that workbook, 1. Start again with a completely new design philosophy or 2. Pop down to PC World and purchase a new Acer 64QuBit Laptop.

    Steve, this is the code:

    Sub DelPOs()
        Dim arS1 As Variant, arS2 As Variant, lr As Long, i As Long, j As Long, sh As Worksheet
        Dim dic As New Scripting.Dictionary, ky As Variant, tm#
        Dim rws As String, arRws As Variant
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        tm = Timer
        'Get list of PO's to search for
        lr = Sheets("Accrual & PO Data").Cells(Rows.Count, 1).End(3).Row
        arS1 = Sheets("Accrual & PO Data").Range("A1:A" & lr)
        For i = 2 To UBound(arS1)
            If Not dic.Exists(arS1(i, 1)) Then dic.Add arS1(i, 1), Nothing
        Next
        uf1.Show
        'Loop through sheets
        For Each sh In ThisWorkbook.Worksheets
            
            'Don't include the following sheets
            
            
           If sh.Name <> "Instructions" And _
           sh.Name <> "Accrual & PO Data" And _
           sh.Name <> "Tab Name List" And _
           sh.Name <> "Subtotal Macro Button" And _
           sh.Name <> "Input Date" And _
           sh.Name <> "Summary FY19 F1(5)" And _
           sh.Name <> "Summary FY19 F1(4)" And _
           sh.Name <> "Summary FY19 F1(3)" And _
           sh.Name <> "Summary FY19 F1(2)" And _
           sh.Name <> "Summary FY19 F1" And _
           sh.Name <> "EP Local" And _
           sh.Name <> "Driver Definitions" And _
           sh.Name <> "EP Global" Then
           
           uf1.Label1 = sh.CodeName & " (" & sh.Name & ")"
           DoEvents
            
                'Get list of PO's on current sheet
                lr = sh.Cells(Rows.Count, 1).End(3).Row
                If lr < 3 Then lr = 3 'There are blank sheets!
                arS2 = sh.Range("A1:A" & lr)
                'Loop through search PO's
                rws = ""
                For Each ky In dic.Keys
                    'Loop through sheet PO's
                    For j = UBound(arS2) To 3 Step -1
                        'If there is a PO match, delete row
                        If ky = arS2(j, 1) Then Worksheets(sh.Name).Rows(j).EntireRow.Delete shift:=xlUp
                    Next
                Next
            End If
        Next
        Unload uf1
        MsgBox Timer - tm & " seconds to complete."
    End Sub
    It takes an age to run (every line it deletes takes just under 2 seconds!)

    When the code has finished, the workbook wheezes for about 20 minutes trying to get over the shock of something worthwhile happening to it!

    I put a userform in there which lets you know which sheet it is working on and the fact that it hasn't gone to sleep.

    Seriously, re-think that workbook and its' pitfalls and then start again with fresh views. There are loads of people here willing to point you in the right direction, just ask... and, more importantly, take in the advice they give, it's priceless.

    Attached is a slimmed down version of your file (I ran a script to delete all the empty rows on each sheet) with the uf in it.
    Attached Files Attached Files
    Semper in excretia sumus; solum profundum variat.

  4. #84
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. Define the references on a sheet, small DB so that you can take decode fields into 'people-speak'. e.g.

    Account = 1000 and see user = Bill
    Account = 2000 and see user = Tom


    2. Start with raw / unprocessed data either on one sheet or by using macro to pull it in from other sources / workbooks

    3. Macro to process raw data sheet by ...

    deleting rows or columns
    formatting
    adding new columns using decodes above
    calculating any fields that will make reporting easier as VALUES, e.g. take PO date (5/1/2020) and store FY (05-2020) in a new column
    Use SUM, SUMIF, COUNTIF, etc. as Application.WorksheetFunction if needed (intrinsic functions are faster than a VBA loop)

    4. Use pivot tables to make analysis easier

    5. Use macros to generate highly formatted 'reports' in separate workbooks, or l like to generate a report in a worksheet in the main book, but save it as a PDF and then delete the temporary worksheet from the main book
    ---------------------------------------------------------------------------------------------------------------------

    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

Posting Permissions

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