VBA -How to properly Find, Copy and Paste a Search from a Command Button on a Userform?

Question

I need some direction as to what could be going wrong with the VBA code that I am using. I have been working at this for hours and can’t seem to figure out what is going on. As of right now when I run the code, nothing happens, no errors, nothing…

A lot of the code I am using I got from this post: Similar Use Case

Any help would greatly be appreciated.

What I am trying to do:

I am trying to search a database for the values in textboxes on a userform when pressing a command button. In other words, I am telling vba to search through rows of data and match the values in textboxes, then if there is a match, copy a that match to a new sheet.

Process:

1. Have a click event for the “Run Check” button on the UserForm Code
2. Clear the target sheet area before each run (Each Click).
3. Set an array from the textbox values where the index of each matches the column number to search (Although I am only searching 2 values in the array, I want to build upon this later so an array made sense)
4. Filter search for only rows that have the status of “Open” in the Status Column
5. One row at a time, compare the value of the appropriate column to the array index that matches it
6. If a match is found, the “match” variable is set to true
7. Loop through the rest of the Textboxes values from the array, if ANY of them don’t match, the “match” variable is set to false, and break the loop over the Textboxes as fail.
8. If “match” is true by the end of the loop through the ROW of the “searched” worksheet, columns 1 to 8 get looped through, setting the values from the searched sheet to the target Sheet.
9. Nest Row finish loop

Screenshots to help with context

Step 1

Step 2

Step 3

Step 4

The Code Updated<-Working:

Private Sub run_check_but_Click()
Const COL_STATUS As Long = 4
Dim wsData As Worksheet, wsSyn As Worksheet
Dim tRow As Long, i As Long
Dim tempList(1 To 9)
Dim match As Boolean
Dim rCol As Range, c As Range

Set wsData = Sheets("Database")
Set rCol = wsData.Range(wsData.Cells(3, 4), wsData.Cells(100, 4))

'Set TargetSheet and clear the previous contents
Set wsSyn = Sheets("Syn_Calc")
wsSyn.Range("A3:G" & wsSyn.Range("A" & Rows.count).End(xlUp).row + 1).ClearContents 'changed from  to 3
tRow = 3

'Set an array of strings, based on the index matching the column to search for each
tempList(5) = curbase_box.Text       'Column "E" (5)
tempList(6) = dirquote_box.Text       'Column "F" (6) 'changed from 9 to 6

For Each c In rCol.Cells
    With c.EntireRow
        If .Cells(COL_STATUS).Value = "Open" Then

            match = False

            For i = LBound(tempList) To UBound(tempList)
                If tempList(i) <> "" Then
                    match = (.Cells(i).Text = tempList(i))
                    If Not match Then Exit For
                End If
            Next i

            If match Then
                'copy values from E-K
                wsSyn.Cells(tRow, 1).Resize(1, 7).Value = _
                     .Cells(5).Resize(1, 7).Value
                tRow = tRow + 1
            End If

        End If 'open
    End With
Next c
End Sub

Answer 1

Untested:

Private Sub run_check_but_Click()

    Const COL_STATUS As Long = 4
    Dim wsData As Worksheet, wsSyn As Worksheet
    Dim tRow As Long, i As Long
    Dim tempList(1 To 9)
    Dim match As Boolean
    Dim rCol As Range, c As Range

    Set wsData = Sheets("Database")
    Set rCol = wsData.Range(wsData.Cells(3, 4), wsData.Cells(100, 4))

    'Set TargetSheet and clear the previous contents
    Set wsSyn = Sheets("Syn_Calc")
    wsSyn.Range("A8:F" & wsSyn.Range("A" & Rows.Count).End(xlUp).Row + 1).ClearContents
    tRow = 3 '<< but you clear from row 8 down?

    'Set an array of strings, based on the index matching the column to search for each
    tempList(5) = curbase_box.Text       'Column "E" (5)
    tempList(9) = dirquote_box.Text       'Column "I" (9)

    For Each c In rCol.Cells
        With c.EntireRow
            If .Cells(COL_STATUS).Value = "Open" Then

                match = False

                For i = LBound(tempList) To UBound(tempList)
                    If tempList(i) <> "" Then
                        match = (.Cells(i).Text = tempList(i))
                        If Not match Then Exit For
                    End If
                Next i

                If match Then
                    'copy values from E-K
                    wsSyn.Cells(tRow, 1).Resize(1, 7).Value = _
                         .Cells(5).Resize(1, 7).Value
                    tRow = tRow + 1
                End If

            End If 'open
        End With
    Next c
End Sub