Range search and return
Results 1 to 4 of 4

Thread: Range search and return
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Location
    UK
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Range search and return

    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 by test3xc31; Jul 20th, 2019 at 04:14 PM.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Range search and return

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Jun 2019
    Location
    UK
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Range search and return

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

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,083
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Range search and return

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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