CODEWITHSUNDEEP
excelvbamacros
DigitalSea
DigitalSea

Reputation: 191

Changing value based on matching value on different worksheet

This code only works properly when I'm on the worksheet("Jobs") sheet where the Master Data is. The textbox and macro button is on worksheet("ID"). When I run the code when I'm on worksheet("ID"), I only get the MsgBox message and code ends. It doesn't update the value when conditions are true. Can someone please look over code to see what the problem is? Having trouble figuring this out.

Thank you.

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

Upvotes: 1

Views: 71

Answers (3)

user3598756
user3598756

Reputation: 29421

I guess you're mostly after this code:

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

while it's not clear to me the role of the macro button ... on worksheet("ID").: if that macro is supposed to be called via that button only, then your ActiveSheet will always be "ID" worksheet

Upvotes: 3

mojo3340
mojo3340

Reputation: 549

agree with @atclaus above, change to the following: 

If Txt <> "" Then
    With sheets("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
                If cell.Text <> Txt Then
                    Exit Sub
                End If
            End If
        Next cell
    End With
 End If

let us know how you get on :)

Upvotes: 2

atclaus
atclaus

Reputation: 1176

Your line For Each cell In .Range("JobCol_Master") is trying to refer to sub range of MasterData range which I do not believe exists. Either use MasterData as a workbook reference, or specify the JobCol_Master explicitly.

Upvotes: 3

Related Questions