Which is the best method to use
 

I am trying to write a code that does the following:

If the value in Range(B3) or Range(B4) is changed perform some calculations
in the worksheet.

But everytime I try to run the code, when it gets to:
..Range("I36") = c.Offset(0, 1).Value
it jumps back to the beginning again which I assume is because the value in
that cell is changed and the Sub is running itself again which is going to
end up being as infinite loop. How can I write a code that does what I want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
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") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
End If

End Sub

Which is the best method to use
 

temporarily disable the events...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
application.enableevents = false
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") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
application.enableevents = true
End If

--
HTH...

Jim Thomlinson


"Ayo" wrote:

I am trying to write a code that does the following:

If the value in Range(B3) or Range(B4) is changed perform some calculations
in the worksheet.

But everytime I try to run the code, when it gets to:
.Range("I36") = c.Offset(0, 1).Value
it jumps back to the beginning again which I assume is because the value in
that cell is changed and the Sub is running itself again which is going to
end up being as infinite loop. How can I write a code that does what I want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
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") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
End If

End Sub

Which is the best method to use
 

Thanks so much. Worked great.

"Jim Thomlinson" wrote:

temporarily disable the events...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
application.enableevents = false
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") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
application.enableevents = true
End If

--
HTH...

Jim Thomlinson


"Ayo" wrote:

I am trying to write a code that does the following:

If the value in Range(B3) or Range(B4) is changed perform some calculations
in the worksheet.

But everytime I try to run the code, when it gets to:
.Range("I36") = c.Offset(0, 1).Value
it jumps back to the beginning again which I assume is because the value in
that cell is changed and the Sub is running itself again which is going to
end up being as infinite loop. How can I write a code that does what I want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
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") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
End If

End Sub

Which is the best method to use
 

Spoke too soon. It just worked the first time I tried it. Now it doesn't do
anything. The values are not changing; they just remain the same when I
change Range("B3"). Any ideas?

"Jim Thomlinson" wrote:

temporarily disable the events...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
application.enableevents = false
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") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
application.enableevents = true
End If

--
HTH...

Jim Thomlinson


"Ayo" wrote:

I am trying to write a code that does the following:

If the value in Range(B3) or Range(B4) is changed perform some calculations
in the worksheet.

But everytime I try to run the code, when it gets to:
.Range("I36") = c.Offset(0, 1).Value
it jumps back to the beginning again which I assume is because the value in
that cell is changed and the Sub is running itself again which is going to
end up being as infinite loop. How can I write a code that does what I want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
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") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
End If

End Sub

Which is the best method to use
 

Never mind Jim, looks like its working again. Thanks for all your help.

"Jim Thomlinson" wrote:

temporarily disable the events...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
application.enableevents = false
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") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
application.enableevents = true
End If

--
HTH...

Jim Thomlinson


"Ayo" wrote:

I am trying to write a code that does the following:

If the value in Range(B3) or Range(B4) is changed perform some calculations
in the worksheet.

But everytime I try to run the code, when it gets to:
.Range("I36") = c.Offset(0, 1).Value
it jumps back to the beginning again which I assume is because the value in
that cell is changed and the Sub is running itself again which is going to
end up being as infinite loop. How can I write a code that does what I want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
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") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
End If

End Sub