VBA Search Criteria, If Match found, Copy and Paste 2 different sets of data into a new table

I have been stuck on this problem for a couple of weeks now and I am reaching out for help and guidance on what to do next and hopefully finally have a solution. Searching through this website provided a lot of great solutions but none that I can get working right.

Task:

Search through Columns A & B for the Search Criteria located in Column K3 and L4

If there is a Match, copy paste columns D-F and N3-P3 into a new table(K9-P9)

Visual Screenshot of what I am trying to do:

Current Code:

Option Explicit

Dim base_Position, reverse__BaseDirection As Range
Dim searchCol_AB, rangeUnion_Copy, rangeUnion_Paste As Range
Dim Cell As Object
Dim wsSyn As Worksheet

Set wsSyn = Sheets("Syn_Calc")

Set base_Position = wsSyn.Range("K3")
Set reverse__BaseDirection = wsSyn.Range("L4")

searchCol_AB = wsSyn.Range("A3:B100")
Set rangeUnion_Copy = Union(Cells(, 4), Cells(, 5), Cells(, 6))
Set rangeUnion_Paste = Union(Cells(, 11), Cells(, 12), Cells(, 13))


For Each Cell In searchCol_AB
   If Cell.Value = UCase(base_Position) And UCase(reverse__BaseDirection) Then

   rangeUnion_Copy.Copy rangeUnion_Paste.Address

  End If
Next
End Sub

Problem:

I am not sure that the solution I currently have will do what I want it to do. I think there may be a better option then using Ucase.
Immediate issue right now is that the code is stuck on an error" Object Required". I can't seem to see why this is happening.

Note: I haven't included the code to copy data from N3:P3 yet since I can't get the first part working.

Any help would greatly be appreciated.

Thanks in advance April

Solution

Please find below the your own code, fully commented. Sorry, I didn't get as far as to check whether it could actually do what you want if it were corrected.

Sub CurrentCode()

    Dim wsSyn As Worksheet
'    Dim base_Postion, reverse__BaseDirection As Range
    Dim base_Position, reverse__BaseDirection As Range
    Dim searchCol_AB, rangeUnion_Copy, rangeUnion_Paste As Range
    Dim Cell As Object

    Set wsSyn = Sheets("Syn_Calc")
    ' Since base_Position is a variant the line below assigns a range to it.
    Set base_Position = wsSyn.Range("K3")
    ' reverse__BaseDirection is declared as a range.
    ' Therefore this cariable is assigned a range as well
    Set reverse__BaseDirection = wsSyn.Range("L4")

    ' ==================================================
        ' The above concept is probably wrong.
        ' If you wish to search for the values in K3 and L4
        ' you should have declared both variables as Strings or Variants
        ' and assigns the respective ranges' Values to them, such
        ' base_Position = wsSyn.Range("K3").Value
    ' ==================================================


    ' searchCol_AB is declared as a Variant and can therefore be assigned anything.
    ' If you wanted to assign a range to it, the Set statement would be required, like
    ' Set searchCol_AB = wsSyn.Range("A3:B100")
    ' since you omit the Set statement VBA will presume that you mean to
    ' assign the property of wsSyn.Range("A3:B100").
    ' Since you don't specify which property you mean, VBA will assign
    ' the default property which is Value.
    searchCol_AB = wsSyn.Range("A3:B100")

    ' ==================================================
        ' The above facts propbably don't fit your intentions.
        ' Since searchCol_AB is intended to be searched it should be a range.
        ' Therefore: Dim searchCol_AB As Range
        ' and Set searchCol_AB = wsSyn.Range("A3:B100")
    ' ==================================================

    ' rangeUnion_Copy is dimensioned as a Variant
    ' Yes, you can assign a range object to it but why not declare it as a Range?
    Set rangeUnion_Copy = Union(Cells(, 4), Cells(, 5), Cells(, 6))
    Set rangeUnion_Paste = Union(Cells(, 11), Cells(, 12), Cells(, 13))

    ' ==================================================
        ' Cells(, 4) misses the row number.
        ' VBA will correct the error and insert 1 instead.
        ' Therefore, Union(Cells(, 4), Cells(, 5), Cells(, 6))
        ' is equivalent to Union(Cells(1, 4), Cells(1, 5), Cells(1, 6))
        ' is equivalent to Range("D1:F1")

        ' Application.Union creates a range consisting of incontiguous cells
        ' like Union(Cells(1, 4), Cells(1, 6))
        ' Range("D1:F1") is just a normal range, not a Union.
        ' you might use Set rangeUnion_Copy = Range(Cells(1, 4), Cells(1, 6))
        ' if you need to change thr rows in a loop that might look like
        ' Set rangeUnion_Copy = Range(Cells(R, 4), Cells(R, 6))
        ' where R is the For ... Next loop counter
    ' ==================================================


    ' your code fails on the next line because
    ' searchCol_AB holds the values of range wsSyn.Range("A3:B100"),
    ' not the range itself.
    For Each Cell In searchCol_AB
        ' ==================================================
            ' A range can have many cells.
            ' the smallest range has a single cell.
            ' Ergo, a cell is a range.
            ' True, a range is an object.
            ' By declaring Cell as an Object you are employing what
            ' is called "late binding", meaning VBA only finds out
            ' which kind of object (Range) it is when it is used.
            ' You might find late binding beyond the scope of your current
            ' knowledge and therefore declare a range as a range for the time being.
        ' ==================================================

        If Cell.Value = UCase(base_Position) And UCase(reverse__BaseDirection) Then
            ' base_Position is a variable of variant type to which a range was assigned.
            ' Therefore base_Position is a range object.
            ' UCase([Range object]) is impossible.
            ' Therefore VBA executes UCase([Range_object.Value])
            ' That gives the result you intend but in a way you didn't understand.
            ' Better to avoid the use of default properties.

        ' ==================================================
            ' If Cell.Value = UCase(base_Position) And UCase(reverse__BaseDirection) Then
            ' is a common logical error. The correct syntax is
            ' If Cell.Value = UCase(base_Position) And Cell.Value = UCase(reverse__BaseDirection) Then
            ' some programmers would add parentheses for better clarity, thus:-
            ' If (Cell.Value = UCase(base_Position)) And (Cell.Value = UCase(reverse__BaseDirection)) Then
            ' Note that each expressions is evaluated to True or False.
        ' ==================================================

            ' The Copy command requires specification of a Destination cell.
            ' Destination is either a single cell (better, since less prone to errors)
            ' or a range of equal size to the copied range.
            ' It can't be a string, such as rangeUnion_Paste.Address
            ' (The Address property always holds a String)
            rangeUnion_Copy.Copy rangeUnion_Paste.Address

        ' ==================================================
            ' The way you have set rangeUnion_Paste it is of equal size
            ' to rangeUnion_Copy. Therefore either of the following would work.
            ' rangeUnion_Copy.Copy Destination:= Range(rangeUnion_Paste.Address)
            ' rangeUnion_Copy.Copy Destination:= rangeUnion_Paste
            ' rangeUnion_Copy.Copy Destination:= rangeUnion_Paste.Cells(1)
        ' ==================================================

        End If
    Next Cell           ' better specify which "Next" is called
End Sub