Worksheet_Change doesn't get triggered

[Limone]

I've got a list of names (A1:A4) and one of objects right next to it (B1:B4). Every time the list of names changes I would like Excel to
1) check if the name that was just entered exists within range J1:J4
2) if it does, enter its matching value (K1:K4) in the cell right next to it (so, column B). Else, exit the sub and let the end user enter whatever they want.

When I try to run the code nothing happens. I know I'm triggering the correct cells, that the value does exist in the J1:K4 range and that Excel recognizes "Activecell" as the one that was just edited but I haven't been able to verify much else. Could anyone help me?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A4")) Is Nothing Then GoTo Line1
Line1:
    If Application.WorksheetFunction.CountIf(Range("J1:J4"), ActiveCell.Value) = True Then
    ActiveCell.Offset(0, 1) = WorksheetFunction.Index(Sheets("Foglio2").Range("J1:K4"), WorksheetFunction.Match(ActiveCell.Value, Sheets("Foglio2").Range("J1:J4"), 0), 2)
        If Not Application.WorksheetFunction.CountIf(Range("J1:J4"), ActiveCell.Value) = True Then
            Exit Sub
        End If
    End If
End Sub

 

[Limone]

Excel wouldn't let me execute the code unless I included a GoTo line in my code, that's why I added it.

 

[steve the fish]

Not quite sure what you mean by that. Do you mean nothing happened? From that it seems you arent understanding the intersect either? Thats used to tell the code to only trigger if, in your case, A1:A4 is changed.

 

[Limone]

I get what target is, I should've used that instead of activecell. Yesterday evening Excel wouldn't accept the COUNTIF part of my code because there was "If" in front of it, right after "Then", for some reason. The only way to make it work was to include a GoTo line.

 

[Limone]

It's me again. I've tried to adapt the code you provided to the actual workbook I need it for but I can't get it to work. Could be because the Index/Match range is on a different sheet than the Target one or should I reference the column tables in some other way?

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rng As Range, c As Range, myMatch As Variant


Set rng = Intersect(Target, Worksheets("Contract Data").Range("ContractTable[Departments]"))


If Not rng Is Nothing Then
    For Each c In rng
        If Len(c.Value) = 0 Then
            c.Offset(0, 1).Value = ""
        Else
            myMatch = Application.Match(c.Value, Worksheets("#DepartmentAddresses").Range("TableAddresses[Department]"), 0)
            If Not IsError(myMatch) Then
                c.Offset(0, 1).Value = Application.Index(Worksheets("#DepartmentAddresses").Range("TablesAddresses[Address]"), myMatch)
            End If
        End If
    Next
End If


End Sub

 

[steve the fish]

Firstly its impossible for the intersect of target(which is on the worksheet where the code resides) and a range off another worksheet to ever produce a result. They can never intersect. In other words a range off one worksheet can never intersect with a range off another worksheet.

 

[Limone]

How do you suggest I achieve what I'm trying to do, the exact way your code did but with the range subjected to the change event and the one I want the values to be retrieved from (in case there's a match) in two different sheets?

 

[offthelip]

try this code which is a slight modification of my original solution:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A4")) Is Nothing Then
With Worksheets("Foglio2")
Namarr = .Range(.Cells(1, 10), .Cells(4, 11))
End With
For i = 1 To 4
 If Target.Value = Namarr(i, 1) Then
  Application.EnableEvents = False
  Cells(Target.Row, 2) = Namarr(i, 2)
  Application.EnableEvents = True
  Exit For
 End If
Next i
End If
End Sub