Consulting

Page 1 of 5 1 2 3 ... LastLast
Results 1 to 20 of 84

Thread: VBA - Search For Value Across Multiple Worksheets

  1. #1

    VBA - Search For Value Across Multiple Worksheets

    Hi VBA Experts,

    I wrote code that I except it to find a value that exists in multiple tabs and return a specific cell where that match exists. Here is the code, and I have attached a small version of the document with the code. In this example I expected to have the program return the worksheet name and the PO's found in each of those tabs. However, It is only picking up the first tab, but yet there is data in both tabs for the instance of PO 235204.

    Sub Multiple_Sheet_Count_Match()


    Application.ScreenUpdating = True


    totalsheets = Worksheets.Count
    POs = Worksheets("End Result").Cells(2, 4).Value
    For i = 1 To totalsheets
    If Worksheets(i).Name <> "End Result" Then
    lastrow = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To lastrow
    If Worksheets(i).Cells(j, i).Value = POs Then
    Worksheets("End Result").Activate

    lastrow = Worksheets("End Result").Cells(Rows.Count, 1).End(xlUp).Row

    Worksheets("End Result").Cells(lastrow + 1, 1).Value = Worksheets(i).Name
    Worksheets("End Result").Cells(lastrow + 1, 2).Value = Worksheets(i).Cells(j, 1).Value
    End If

    Next
    End If
    Next


    Application.ScreenUpdating = False

    End Sub



    Any ideas would be greatly appreciated.

    Thanks.
    Steve
    Attached Files Attached Files

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Change the i for a 1 in:

                    If Worksheets(i).Cells(j, 1).Value = POs Then
    Semper in excretia sumus; solum profundum variat.

  3. #3
    paulked,

    Thank you! One more idea would be appreciated. What if I want to look at all of the purchase orders in the first tab, with some sort of loop formula or another if then else, and look in the second tab to see where there are matches. Then return that list of matches to another tab. Could it be a set of code that is built around this code? The ultimate goal is to find all matches, and it my larger workbook I will need to look at 111 worksheets to find matches with that first tab to another worksheet. I only care about matches with the PO's, column A.

    Any ideas?

    Thank you for your help.

    Steve

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Sure. I would use two arrays and a dictionary. An example for the two sheets supplied with your workbook would be:

    Sub test()
        Dim arS1, arS2, lr As Long, i As Long, j As Long
        Dim dic As New Scripting.Dictionary, kys()
        lr = Sheet1.Cells(Rows.Count, 1).End(3).Row
        arS1 = Sheet1.Range("A1:A" & lr)
        lr = Sheet2.Cells(Rows.Count, 1).End(3).Row
        arS2 = Sheet2.Range("A1:A" & lr)
        For i = 2 To UBound(arS1)
            For j = 3 To UBound(arS2)
                If arS1(i, 1) = arS2(j, 1) Then
                    With dic
                        If Not .Exists(arS2(j, 1)) Then .Add arS2(j, 1), Nothing
                    End With
                End If
            Next
        Next
        kys = dic.Keys
        Sheet4.Range("E5").Resize(dic.Count) = Application.Transpose(kys)
    End Sub
    Attached Files Attached Files
    Semper in excretia sumus; solum profundum variat.

  5. #5
    Paulked,

    Thank you. I am not familiar with using a dictionary in VBA. Is there a way to do a loop on the suggested 6732-Test Macro file code that you did? Something that goes through a repeats for every workbook to look for matches? And if so, is there a way to "Rows(1).EntireRow.Delete" for those matching items that have been found in the "PO Accrual Data" worksheet? Instead of listing the matches, it would delete the entire row in the PO Accrual Data tab.

    Thanks.
    Steve

  6. #6
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Add another loop to go through worksheets and instead of using the dictionary, delete the items within the loop.

    Sorry, but I'm pressed for time to give you an example. If you post your workbook I'll have a look later/tomorrow morning.
    Semper in excretia sumus; solum profundum variat.

  7. #7
    paulked,

    Ok I will try that. Quick question on the macro you suggested gets hung up on this line of code: Windows("67340-2020-2021 Workbook V. 2.xlsm").Activate

    It is looking or another file. But when I click on your macro button it runs fine. Any idea why?

    Thanks.
    Steve

  8. #8
    Paulked,

    Is this what you suggested? I tried and it stops working at "totalsheets = Worksheets.Count".

    I just nested your formula inside the If Then

    totalsheets = Worksheets.Count

    Thanks for the help.

    Steve

  9. #9
    Here is the code, which isn't working.

    Sub Find_PO_Matches3()


    Dim lr
    lr = Worksheets("End Result").Cells(Rows.Count, 1).End(xlUp).Row
    Application.ScreenUpdating = True


    totalsheets = Worksheets.Count
    POs = Worksheets("End Result").Cells(2, 4).Value
    For i = 1 To totalsheets
    If Worksheets(i).Name <> "End Result" Then
    LastRow = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To LastRow
    If Worksheets(i).Cells(j, 1).Value = POs Then

    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets("Test Data Set").Select
    Range("C73,A3").Select
    Range("A3").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-310
    Windows("67340-2020-2021 Workbook V. 2.xlsm").Activate
    Windows("67342-Test Macro - Delete Used POs.xlsm").Activate

    End If

    Next
    End If
    Next


    End Sub
    Attached Files Attached Files

  10. #10
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    What code is that? (btw, you should enclose any code in Code Tags (# on the editor menu)

    If you don't want to post your workbook then I'll need to know where the data is on each of the 111 sheets.
    Semper in excretia sumus; solum profundum variat.

  11. #11
    paulked,

    Thanks for the response. I truly appreciate your expertise!

    I am not sure what you mean by # on the editor menu. I will go figure that out.

    I don't want to post that workbook because it is just too big. So, the second attachment has a few tabs with data to help me figure out and test the best way to do this:

    What I am looking to do is compare each column A in each worksheet to column A in the "PO Accrual Data" worksheet. If there is a match then I would like to delete that row in the "PO Accrual Data" worksheet.

    Thanks again for any help.

    Steve

  12. #12
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    67341.jpg

    Backup before using it!!!!
    Here's the code:

    Sub test1()
        Dim arS1, arS2, lr As Long, i As Long, j As Long, x As Long, sh As Worksheet
        Dim dic As New Scripting.Dictionary, kys()
        lr = Sheet1.Cells(Rows.Count, 1).End(3).Row
        arS1 = Sheet1.Range("A1:A" & lr)
        For Each sh In ThisWorkbook.Worksheets
            If sh.Name <> "PO Accrual Data" Then
                lr = sh.Cells(Rows.Count, 1).End(3).Row
                If lr < 3 Then GoTo Nxt
                For i = 2 To UBound(arS1)
                    For j = lr To 3 Step -1
                        If arS1(i, 1) = sh.Cells(j, 1) Then
                        sh.Range("A" & j).EntireRow.Delete
                        End If
                    Next
                Next
            End If
    Nxt:
        Next
    End Sub
    Semper in excretia sumus; solum profundum variat.

  13. #13
    paulked,

    You have been so helpful!! Now I understand what you mean by the # sign. Thank you so I can continue to participate on this forum as I am new.

    As far as the macro. My intention was delete the row in the ""PO Accrual Data" worksheet if there was a match in the other worksheets column A. Not the other tabs. What would I code to make that happen? So for example if I find a match with column A in "PO Accrual Data" worksheet with any other worksheet, how would I delete that row in the "PO Accrual Data" worksheet? I need all other tabs to remain as is.

    Your skills are amazing.

    Thanks.
    Steve

  14. #14
    And keep all of the data on all the other worksheets not named "PO Accrual Data".

  15. #15
    This code is deleting all of the other tabs of rows data instead of all the other tabs. I was trying to delete the "PO Accrual Data" row when it found an instance of a match in another tab. How do I do that code?

    You have been an incredible help!

    Steve

  16. #16
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Sorry, I misread your request.

    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 = Sheet1.Cells(Rows.Count, 1).End(3).Row
        arS1 = Sheet1.Range("A1:A" & lr)
        'Loop through sheets
        For Each sh In ThisWorkbook.Worksheets
            'Don't include PO Accrual Data
            If sh.Name <> "PO Accrual Data" 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 Sheet1.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!
            Sheet4.Range("E5").Resize(dic.Count) = Application.Transpose(kys)
        End If
        Sheet4.Range("E" & dic.Count + 6) = Timer - tm & " seconds to complete."
    End Sub
    Attached Files Attached Files
    Semper in excretia sumus; solum profundum variat.

  17. #17
    Paulked,

    This is awesome! I have learned a lot from you. One last question. Where can I see the dictionary or is this an SQL running in the back ground?

    Thank you.
    Steve

  18. #18
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Glad to be of help

    The dictionary is printed to Sheet4 E5 down. To view it 'live' you have to be in break mode and look at the Locals window (or put it in the Watch window)
    Semper in excretia sumus; solum profundum variat.

  19. #19
    Paulked,

    When I click on View and then watch window nothing appears. I don't see the window on the bottom of the VBA screen. Any idea how to fix this?

    Any ideas on how to fix this? I had the same issue with the Immediate Window.

    Thanks,
    Steve

  20. #20
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Have you googled it?
    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
  •