Range search and return

test3xc31

New Member
Joined
Jun 11, 2019
Messages
27
Office Version
  1. 2021
Platform
  1. Windows
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.
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try:
Code:
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
 
Upvote 0
You are actually a genius. That worked first time, no changes. Thankyou!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top