Consulting

Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast
Results 41 to 60 of 84

Thread: VBA - Search For Value Across Multiple Worksheets

  1. #41
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Back to the posting the workbook issue! You need to set the reference to the Microsoft Scripting Runtime.
    Semper in excretia sumus; solum profundum variat.

  2. #42
    Darn. I am not sure what that means?

  3. #43
    ok. I found that setting. Now I get this. See the attachment.
    Attached Images Attached Images

  4. #44
    The Data I want to search / reference is in column A of the "Accrual & PO Data" worksheet. So I tried to change your code to Sheet 2. The highlighted yellow in the thumbnail.

    That did not work. What am I doing wrong?

    Thanks a ton for your expertise! I think that you have me so close to solving my need.

    Steve

  5. #45
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    It compiled but I haven't tested it because the sheet numbers have changed, that code refers to Sheet2 which doesn't exist in this workbook.
    From post #39

    Then on the next line you've changed that to Sheet1!!

    They need to be changed to either the correct sheet number for the PO sheet OR changed to:

    lr = Sheets("Accrual & PO Data").Cells...
    etc
    Semper in excretia sumus; solum profundum variat.

  6. #46
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    In the last workbook you sent me, the sheet was Sheet153

    IF they are the first 13 Sheets in the workbook then you can use

        lr = Sheet153.Cells(Rows.Count, 1).End(3).Row
        arS1 = Sheet153.Range("A1:A" & lr)
        'Loop through sheets
        For Each sh In ThisWorkbook.Worksheets
            'Don't include 1st 13 sheets
            If sh.Index > 13 Then
                'Get list of PO's on current sheet
    Last edited by paulked; 05-14-2020 at 03:58 AM.
    Semper in excretia sumus; solum profundum variat.

  7. #47
    Paulked,

    OK. I will try this tonight. My intention was to skip the first 13 worksheets and not run your code on those. But, the "PO Accrual" worksheet is still needed as the reference to compare all the other worksheets against the PO # column A and then delete out of that worksheet any matches.

    I am not sure what you mean about sheet 153? That was the last worksheet in that big file. I need to look at all worksheets (except the first 13) and then compare them to column A in the "PO Accrual" tab.

    Will your additional code above accomplish this?

    Thanks again for all your help.
    Steve

  8. #48
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Sheets can be addressed by either their sheet name (tab name) or their code name.

    I normally address them by their code name as the sheet name can easily be changed by the user (as you did with "PO Accrual Data" in post #24 to "Accrual & PO Data" in post #28).

    The code name is the name in the Project Explorer before the brackets, the sheet name is the name you see in brackets in the VBE or on the tab of the sheet in Excel:

    67341b.png

    The Sheet153 code in post #46 can be replaced by either the code name for the PO Accrual sheet or Sheets("Accrual & PO Data") if it is still named that.

    IF the first 13 tabs in the workbook are the ones you want to skip, then the code in #46 If sh.Index > 13 Then can be used, otherwise you need to list the sheets you want to skip.
    Semper in excretia sumus; solum profundum variat.

  9. #49
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    IF the first 13 tabs in the workbook are the ones you want to skip, then the code in #46 If sh.Index > 13 Then can be used, otherwise you need to list the sheets you want to skip.
    Since you can't trust users to leave things alone, I'd suggest giving the sheets a meaningful Code Name and using that to determine which sheets to skip

    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

  10. #50
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Thanks Paul, good suggestion, but I doubt that will happen I'm almost through this wall though
    Semper in excretia sumus; solum profundum variat.

  11. #51
    Paulked,

    I have attached the document with the VBA code. I believe I have included all of your suggestions and VBA code. It is now getting hung up on "Dim dic As New Scripting.Dictionary, kys() As Variant, ky As Variant, tm#".

    Why would it get hung up on a Dim?

    Thanks.
    Steve

    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, kys() As Variant, ky As Variant, tm#
        tm = Timer
        'Get list of PO's to search for
        lr = Sheet("Accrual & PO Data").Cells(Rows.Count, 1).End(3).Row
        arS1 = Sheet("Accrual & PO Data").Range("A1:A" & lr)
        'Loop through sheets
        For Each sh In ThisWorkbook.Worksheets
            'Don't include PO Accrual Data
            
            
           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
               
            
            
          
                '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
                For i = 2 To UBound(arS1)
                    'Loop through sheet PO's
                    For j = 3 To UBound(arS2)
                        'If there is a PO match, add it to the dictionary if not already in there
                        If arS1(i, 1) = arS2(j, 1) Then
                            If Not dic.Exists(arS2(j, 1)) Then dic.Add arS2(j, 1), Nothing
                        End If
                    Next
                Next
            End If
        Next
        'Loop through list to delete
        For i = UBound(arS1) To 2 Step -1
            'Loop through dictionary items
            For Each ky In dic.Keys
                'If there is a match. delete the PO row
                If Sheet("Accrual & PO Data").Cells(i, 1) = ky Then Sheet1.Rows(i).Delete shift:=xlUp
            Next
        Next
        'Show deleted PO's
        kys = dic.Keys
        If dic.Count <> 0 Then 'Can't print nothing!
            Sheet1.Range("E5").Resize(dic.Count) = Application.Transpose(kys)
        End If
        Sheet1.Range("E" & dic.Count + 6) = Timer - tm & " seconds to complete."
    End Sub
    Attached Files Attached Files

  12. #52
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    It is probably unable to compile, not hang, because you haven't set reference to the Scripting Runtime.
    Semper in excretia sumus; solum profundum variat.

  13. #53
    How do I set a reference? I have never used Scripting Runtime before.

    Thanks.
    Steve

  14. #54
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    You have! See my post #41
    You need to set the reference to the Microsoft Scripting Runtime.
    and then read your post #43
    ok. I found that setting.
    Anyway, here it is on a nice silver spoon, fresh from Google:

    How do I enable Microsoft Scripting Runtime?

    Setting the Reference to the Microsoft Scripting Runtime Library

    • In the VB Editor, click on Tools.
    • Click on References.
    • In the References dialog box that opens, scroll through the available references and check the 'Microsoft Scripting Runtime' option.
    • Click OK.





    Semper in excretia sumus; solum profundum variat.

  15. #55
    Paulked,

    It worked! One more question. What do I add to the code if I want to delete PO rows if there are multiple instance of that same number in the "PO & Accrual Data" worksheet. For example, there may be more than one in column A such as:

    239618
    239618
    239618
    239618
    239618


  16. #56
    Paulked,

    I run it an I do not get any breaks or error messages. However, it does not delete the rows in the "PO & Accrual Data" worksheet. But, you have the code here. Not sure why it is not executing that step? And it is a loop so it should be deleting all instances in that worksheet.

    Any ideas?

    I have attached the document.

     'Loop through list to delete    For i = UBound(arS1) To 2 Step -1
            'Loop through dictionary items
            For Each ky In dic.Keys
                'If there is a match. delete the PO row
                If Sheet153.Cells(i, 1) = ky Then Sheet278.Rows(i).Delete shift:=xlUp
            Next
        Next
        'Show deleted PO's
        kys = dic.Keys
        If dic.Count <> 0 Then 'Can't print nothing!
            Sheet278.Range("E5").Resize(dic.Count) = Application.Transpose(kys)
        End If

  17. #57
    Paulked,

    Here is the attachment that I forgot to add to the last thread.

    Thanks.
    Steve
    Attached Files Attached Files

  18. #58
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Semper in excretia sumus; solum profundum variat.

  19. #59
    Paulked,

    Ah! That was it, I had the wrong Sheet#! I now put this code into my 35M workbook. It is running, but taking a long time to run. The workbook has a lot of nested formulas which is what probably makes it big. Is there a way to speed it up? May put in Application.Calculation = xlCalculationManual? If so, would I put it in the loop or maybe at the top of the program?

    Thanks for all of your expertise help! How did you learn VBA? I have learned a lot from you. But wondering how you became an expert.

    Steve

  20. #60
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    I'm no expert, but what I read I tend to retain.

    If you are sure it is set to automatic before the code runs then set it to manual at the beginning, then set it back to automatic at the end, don't do it in the loop, it will slow things down even more!!!!!!!!!!!!!
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

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