excel - 根据不同工作表上的匹配值更改值

Question

仅当我在主数据所在的工作表(“工作”)表上时，此代码才能正常工作。文本框和宏按钮位于工作表(“ID”)上。当我在工作表(“ID”)上运行代码时，我只收到 MsgBox 消息并且代码结束。当条件为真时，它不会更新值。有人可以查看代码以查看问题所在吗？很难弄清楚这一点。

谢谢你。

Option Explicit

Sub CloseJob()

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

On Error GoTo errHndl
Txt = ThisWorkbook.Worksheets("ID").TextBoxID.Text
Set MasterData = ThisWorkbook.Worksheets("Jobs").Range("MasterData")

If Txt <> "" Then
    With MasterData
        For Each cell In .Range("JobCol_Master")
            'If job# matches textbox and if job# is to correct region then...
            If cell.Text = Txt And Cells(cell.row, 4).Value = "ID" Then
              Cells(cell.row, 11).Value = "Test"
              Exit Sub
                If cell.Text <> Txt Then
                    Exit Sub
                End If
            End If
        Next cell
    End With
 End If

MsgBox "Job not found."

Exit Sub
errHndl:
    MsgBox "Error happened while working on: " + vbCrLf + _
        vbCrLf + vbCrLf + "Error " + _
        Str(Err.Number) + ": " + Err.Description, vbCritical + vbOKOnly, "Error"
End Sub

Answer 1

我猜你主要是在这个代码之后:

Option Explicit

Sub CloseJob()
    Dim cell As Range
    Dim Txt As String

    On Error GoTo errHndl

    With ThisWorkbook
        Txt = .Worksheets("ID").TextBoxID.Text
        If Txt <> "" Then
            With .Worksheets("Jobs")
                For Each cell In .Range("JobCol_Master")
                    'If job# matches textbox and if job# is to correct region then...
                    If cell.Text = Txt And .Cells(cell.row, 4).Value = "ID" Then
                        .Cells(cell.row, 11).Value = "Test"
                        Exit Sub '<-- remove it if you want to "mark" all job# matches in 'JobCol_Master' named range
                    End If
                Next cell
            End With
        End If
    End With
    MsgBox "Job not found."

    Exit Sub
errHndl:
        MsgBox "Error happened while working on: " + vbCrLf + _
            vbCrLf + vbCrLf + "Error " + _
            Str(Err.number) + ": " + Err.Description, vbCritical + vbOKOnly, "Error"
End Sub

虽然我不清楚宏按钮的作用......在工作表(“ID”)上:如果该宏应该只通过该按钮调用，那么你的 ActiveSheet将永远是“ID”工作表