Which is the best method to use

A

Ayo

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
 
J

Jim Thomlinson

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
 
A

Ayo

Thanks so much. Worked great.

Jim Thomlinson said:
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
 
A

Ayo

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top