Loop through column matching data in workbook and return a value-CodePudding

I have been trying to adapt the following code to Loop through column A of Sheet 1 and for each value in column A search the whole workbook for it's matching value (which will be found in another sheet also in column A). When a match is found, return the value found in the same row but from column F.

Sub Return_Results_Entire_Workbook()
    searchValueSheet = "Sheet2"
    searchValue = Sheets(searchValueSheet).Range("A1").Value
    returnValueOffset = 5
    outputValueSheet = "Sheet2"
    outputValueCol = 2
    outputValueRow = 1

    Sheets(outputValueSheet).Range(Cells(outputValueRow, outputValueCol), Cells(Rows.Count, outputValueCol)).Clear
    wsCount = ActiveWorkbook.Worksheets.Count

    For I = 1 To wsCount
        If I <> Sheets(searchValueSheet).Index And I <> Sheets(outputValueSheet).Index Then
            'Perform the search, which is a two-step process below
            Set Rng = Worksheets(I).Cells.Find(What:=searchValue, _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
                
            If Not Rng Is Nothing Then
                rangeLoopAddress = Rng.Address
            
                Do
                    Set Rng = Sheets(I).Cells.FindNext(Rng)
                    Sheets(outputValueSheet).Cells(Cells(Rows.Count, outputValueCol).End(xlUp).Row   1, outputValueCol).Value = Sheets(I).Range(Rng.Address).Offset(0, returnValueOffset).Value
                Loop While Not Rng Is Nothing And Rng.Address <> rangeLoopAddress
            End If
        End If
    Next I
End Sub

The code above works but only for the first row of data on Sheet1.

Any help would be greatly appreciated!

CodePudding user response：

You can create an array of arrays where each index of main array would be the dataset A:F from each worksheet:

Sub test()
Dim WK As Worksheet
Dim LR As Long
Dim i As Long
Dim j As Long
Dim MasterArray() As Variant
Dim WkArray As Variant

'create master aray
ReDim MasterArray(1 To ThisWorkbook.Worksheets.Count - 1) 'As many indexes as worksheets -1 (because master sheet does not count)
i = 1

For Each WK In ThisWorkbook.Worksheets
    If WK.Name <> "Hoja1" Then 'exclude master sheet witch search values
        LR = WK.Range("A" & WK.Rows.Count).End(xlUp).Row 'last non-blank row
        WkArray = WK.Range("A1:F" & LR).Value 'take all values in A:F to singlearray
        MasterArray(i) = WkArray
        Erase WkArray
        i = i   1
    End If
Next WK

'now in Master array you have in each index all the values
' as example, if you call MasterArray(1)(1, 1) it will return cell value A1 from first worksheet

Set WK = ThisWorkbook.Worksheets("Hoja1") 'master sheet witch search values

With Application.WorksheetFunction
    LR = WK.Range("A" & WK.Rows.Count).End(xlUp).Row 'last non-blank row
    For i = 1 To LR Step 1 'for each row in master sheet until last non blank
        For j = 1 To UBound(MasterArray) Step 1 'for each dataset in masterarray
            WkArray = Application.Transpose(Application.Index(MasterArray(j), , 1)) 'first column of dataset (A column)
            
            If IsError(Application.Match(WK.Range("A" & i).Value, WkArray, 0)) = False Then 'if value exists get F
                WK.Range("B" & i).Value = .VLookup(WK.Range("A" & i).Value, MasterArray(j), 6, 0)
                Erase WkArray
                Exit For
            End If
            
            Erase WkArray
        Next j
    Next i
End With

Erase MasterArray
Set WK = Nothing

End Sub

The code first creates the main array named MasterArray. Then it loops trough each value on column A from Master Sheet (named Hoja1 in my example) and checks if the value exists in each subarray. If it does then returns columns F from dataset and keep looping.

After executing code I get this output:

Notice value 2 returns nothing because it does not exist in any of the other sheets.