| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Method "Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
| Please post this thread a correct full method, method about | New Users to Excel | |||
| Please post this thread a complete correct method, method about te | New Users to Excel | |||
| GetObject method not work after Call Shell Method | Excel Programming | |||
| Why QUIT method doesn't work after COPY method? | Excel Programming | |||
Hybrid Mode
