Range search and return

[test3xc31]

This, I thought would be easy... I'm still very much a novice but I've been learning and I can do it with array formulas so I thought, VBA shouldn't be a problem... one whole Saturday later.

I have a very large and growing range of data, Sheet("Tally"), the source
And another Sheet("DataExtract"), the target

All I would like to do is search "Tally" column "C" for the value specified in "DataExtract" "B2" and "Tally" Column "G" for the value specified in "DataExtract" "B1"

Then if both are a match, return the adjacent value in column "A" for all the matches, as a list in "DataExtract" starting at "A6"

Please any help, or even a pointer in the right direction would great. I've been playing with loops but just can't get anything that remotely even thinks about working.

 

[mumps]

Try:

Sub CopyMatches()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet, i As Long, arr As Variant, str1 As String, str2 As String, x As Long: x = 6
    Set srcWS = Sheets("Tally")
    Set desWS = Sheets("DataExtract")
    str1 = desWS.Range("B2")
    str2 = desWS.Range("B1")
    arr = srcWS.Range("A2", srcWS.Range("G" & Rows.Count).End(xlUp)).Resize(, 7).Value
    For i = 1 To UBound(arr, 1)
        If arr(i, 3) & "|" & arr(i, 7) = str1 & "|" & str2 Then
            desWS.Cells(x, 1) = arr(i, 1)
            x = x + 1
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[test3xc31]

You are actually a genius. That worked first time, no changes. Thankyou!

 

[mumps]

You are very welcome.