Change cell values if a match is found
<p>I have a worksheet that has a textbox with a button that will be assigned to a macro. In this workbook there is another worksheet that holds the master data in a table. The textbox will have a job#. What I'm trying to do is lookup the job# in the job# column (column B/named range: "JobCol_Master") in the master table, and if a match is found, then in that row, verify a value based on what's in column D. If true, then change cell values in particular columns in that row.</p>

<p>I am getting a type mismatch error on line <code>Range(Cells(cell, 11)).Value = "Test"</code>. I am also getting an error when values don't match. </p>

<p>I appreciate any guidance anyone can give me.</p>

<pre><code>Option Explicit

Sub IDCloseJob()

Dim ws As Worksheet
Dim MasterData As Range
Dim sourceID As Range
Dim cell As Range, row As Range, JobCol As Range
Dim Txt As String

Txt = ThisWorkbook.Worksheets("ID").TextBoxID.Text
Set MasterData = ThisWorkbook.Worksheets("Jobs").Range("MasterData")


If Txt &lt;&gt; "" Then
    With MasterData
        For Each cell In .Range("JobCol_Master")
            'If job# matches textbox and if job# has value equal to "ID" in Column D then...
            If cell.Text = Txt Then
              Cells(cell.row, 11).Value = "Test"
            Else
              MsgBox "Job# not found."
            End If
        Next cell
    End With
 End If

End Sub
</code></pre>

<p>Updated Code:</p>

<pre><code>Sub IDCloseJob()

Dim MasterData As Range
Dim sourceID As Range
Dim cell As Range, row As Range, JobCol As Range
Dim Txt As String

Txt = ThisWorkbook.Worksheets("ID").TextBoxID.Text
Set MasterData = ThisWorkbook.Worksheets("Jobs").Range("MasterData")


If Txt &lt;&gt; "" Then
    With MasterData
        For Each cell In .Range("JobCol_Master")
            'If job# matches textbox and if job# has value equal to "ID" in Column D then...
            If cell.Text = Txt Then
              Cells(cell.row, 11).Value = "Test"
            Else
              MsgBox "Job not found."
              Exit Sub
            End If
        Next cell
    End With
 End If
End Sub
</code></pre>

<p>The above code if true doesn't complete the true statement <code>Cells(cell.row, 11).Value = "Test"</code>. It bring up the <code>MsgBox</code> then exits the sub. Not sure why...code looks right. If I leave out the false statement it completes my true statement.</p>