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

 

[Osvaldo Palmeiro]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column > 1 Then Exit Sub
 If Application.CountIf(Range("J1:J4"), Target.Value) Then
  Target.Offset(0, 1).Value = WorksheetFunction.Index(Sheets("Foglio2").Range("J1:K4"), WorksheetFunction.Match(Target.Value, Sheets("Foglio2").Range("J1:J4"), 0), 2)
 End If
End Sub

 

[Limone]

I've tried to play with your suggestion but it always returns an error. This type it's saying the problem is with the countif part of the code.

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range("a1:a4")
 If Target.Column > 1 Then Exit Sub
 If Application.WorksheetFunction.Countif(Range("J1:J4"), Target.Value) Then
  Target.Offset(0, 1).Value = WorksheetFunction.Index(Sheets("Foglio1").Range("J1:K4"), WorksheetFunction.Match(Target.Value, Sheets("Foglio1").Range("J1:J4"), 0), 2)
 End If
End Sub

 

[steve the fish]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

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

Set rng = Intersect(Target, Range("A1:A4"))

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, Range("J1:J4"), 0)
            If Not IsError(myMatch) Then
                c.Offset(0, 1).Value = Application.Index(Range("K1:K4"), myMatch)
            End If
        End If
    Next
End If

End Sub

 

[Osvaldo Palmeiro]

Sorry, the code you posted is different from the code I suggested ...

 

[offthelip]

whenever you are writing to a cells during a worksheet change event you should turn off the application events because writing to the cell will trigger another worksheet change event.
This code will do it using array rather than workhseet functions so it should be faster:

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, 10))
End With
For i = 1 To 4
 If Target.Value = namarr(i, 1) Then
  Application.EnableEvents = False
  Cells(Target.Row, 2) = Target.Value
  Application.EnableEvents = True
  Exit For
 End If
Next i
End If
End Sub

 

[Limone]

I know, that's because your code didn't work on my end. The very first line would always get highlighted, and then the COUNTIF one once I tried to establish what "target" was. I tried to fix it, sorry.

 

[Limone]

steve the fish, your solution worked, thank you very much.

offthelip, your code always returns what was originally entered in the activesheet.

Could any of you please tell me if the code I included in the original question looked fine, and if not what was wrong with it? I'm trying to learn VBA for myself.

 

[steve the fish]

The code you originally posted meant you didnt understand a few things. Firstly the goto line. There is no point in using goto to go to the next line. The code will execute the next line anyway. Thats how code works. You were telling the code to goto the next line when it was going there anyway. If you wanted to skip lines then you could use a goto or an IF test. Secondly it doesnt appear you know what Target is. Target in the worksheet change event is the cell/cells being changed. You wouldnt use Activecell you would use Target.

 

[steve the fish]

Also your range in the countif doesnt have the appropriate sheet name based on the index match.