No error message but code doesn't work
 

I have this Sub that I am trying to run but it keeps going back to the
beginning everytime it get to this line:

..Range("I36") = Application.WorksheetFunction.VLookup(c,
Worksheets("Goals").Range("C3:K54"), 2, False)

no errors, no messages. I can't figure out what the problem is. Any help
will be appreciated. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("CENTRAL PA Charts").Activate
With ActiveSheet
For Each c In Worksheets("Goals").Range("C3:C54").Cells
If c.Value = .Range("B4") And c.Offset(0, -1).Value = .Range("B3")
Then
.Range("I36") = Application.WorksheetFunction.VLookup(c,
Worksheets("Goals").Range("C3:K54"), 2, False)
.Range("I37") = Application.WorksheetFunction.VLookup(c,
Worksheets("Goals").Range("rngGoals"), 4, False)
Exit For
End If
Next c
End With

End Sub

No error message but code doesn't work
 

Temporarily disable events
Application.EnableEvents = false
'your code
Application.EnableEvents = true

One very minor point. You are using activesheet. In this case not a problem
but if somehow you were to change the selected sheet the code could operate
on the wrong sheet. Since this code is embeded directly in a sheet you are
better off to use Me insetad of activesheet. Me will always refer to the
sheet that the code is in.
--
HTH...

Jim Thomlinson


"Ayo" wrote:

I have this Sub that I am trying to run but it keeps going back to the
beginning everytime it get to this line:

.Range("I36") = Application.WorksheetFunction.VLookup(c,
Worksheets("Goals").Range("C3:K54"), 2, False)

no errors, no messages. I can't figure out what the problem is. Any help
will be appreciated. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("CENTRAL PA Charts").Activate
With ActiveSheet
For Each c In Worksheets("Goals").Range("C3:C54").Cells
If c.Value = .Range("B4") And c.Offset(0, -1).Value = .Range("B3")
Then
.Range("I36") = Application.WorksheetFunction.VLookup(c,
Worksheets("Goals").Range("C3:K54"), 2, False)
.Range("I37") = Application.WorksheetFunction.VLookup(c,
Worksheets("Goals").Range("rngGoals"), 4, False)
Exit For
End If
Next c
End With

End Sub