Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correcting "Circular Action" in Worksheet_Change
I'm having a problem with the a Worksheet_Change macro. I want the code to
take the value of a cell perform a goal seek then return that value to null. Here is the code: Sub Worksheet_Change(ByVal Target As Range) Dim sGoal As String, iRow As Integer If Target.Column = 10 And Target.Row 6 And Target.Row < 9 Then sGoal = Target.Value iRow = Target.Row Select Case iRow Case 7 Range("F36").GoalSeek Goal:=sGoal, ChangingCell:=Range("J3") Case 8 Range("J6").GoalSeek Goal:=sGoal * 100, ChangingCell:=Range("J3") End Select 'Range("J" & iRow).Value = "" End If End Sub I'm assuming since it works fine as it is now, and it doesn't work when the 'Range line is activated, that when it goes to change the cell value back to null, it recalls the Worksheet_Change Routine, then gives me an error on the GoalSeek line. Any thoughts on how to do this? Thanks in advance. And I'll be sure to check yes to answers. -- Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correcting "Circular Action" in Worksheet_Change
Sub Worksheet_Change(ByVal Target As Range)
Dim sGoal As String, iRow As Integer If Target.Column = 10 And Target.Row 6 And Target.Row < 9 Then Application.enableevents = false 'New code**** sGoal = Target.Value iRow = Target.Row Select Case iRow Case 7 Range("F36").GoalSeek Goal:=sGoal, ChangingCell:=Range("J3") Case 8 Range("J6").GoalSeek Goal:=sGoal * 100, ChangingCell:=Range("J3") End Select 'Range("J" & iRow).Value = "" Application.enableevents = true 'New code**** End If -- HTH... Jim Thomlinson "Brian" wrote: I'm having a problem with the a Worksheet_Change macro. I want the code to take the value of a cell perform a goal seek then return that value to null. Here is the code: Sub Worksheet_Change(ByVal Target As Range) Dim sGoal As String, iRow As Integer If Target.Column = 10 And Target.Row 6 And Target.Row < 9 Then sGoal = Target.Value iRow = Target.Row Select Case iRow Case 7 Range("F36").GoalSeek Goal:=sGoal, ChangingCell:=Range("J3") Case 8 Range("J6").GoalSeek Goal:=sGoal * 100, ChangingCell:=Range("J3") End Select 'Range("J" & iRow).Value = "" End If End Sub I'm assuming since it works fine as it is now, and it doesn't work when the 'Range line is activated, that when it goes to change the cell value back to null, it recalls the Worksheet_Change Routine, then gives me an error on the GoalSeek line. Any thoughts on how to do this? Thanks in advance. And I'll be sure to check yes to answers. -- Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correcting "Circular Action" in Worksheet_Change
Thanks Jim,
Works like a champ. -- Brian "Jim Thomlinson" wrote: Sub Worksheet_Change(ByVal Target As Range) Dim sGoal As String, iRow As Integer If Target.Column = 10 And Target.Row 6 And Target.Row < 9 Then Application.enableevents = false 'New code**** sGoal = Target.Value iRow = Target.Row Select Case iRow Case 7 Range("F36").GoalSeek Goal:=sGoal, ChangingCell:=Range("J3") Case 8 Range("J6").GoalSeek Goal:=sGoal * 100, ChangingCell:=Range("J3") End Select 'Range("J" & iRow).Value = "" Application.enableevents = true 'New code**** End If -- HTH... Jim Thomlinson "Brian" wrote: I'm having a problem with the a Worksheet_Change macro. I want the code to take the value of a cell perform a goal seek then return that value to null. Here is the code: Sub Worksheet_Change(ByVal Target As Range) Dim sGoal As String, iRow As Integer If Target.Column = 10 And Target.Row 6 And Target.Row < 9 Then sGoal = Target.Value iRow = Target.Row Select Case iRow Case 7 Range("F36").GoalSeek Goal:=sGoal, ChangingCell:=Range("J3") Case 8 Range("J6").GoalSeek Goal:=sGoal * 100, ChangingCell:=Range("J3") End Select 'Range("J" & iRow).Value = "" End If End Sub I'm assuming since it works fine as it is now, and it doesn't work when the 'Range line is activated, that when it goes to change the cell value back to null, it recalls the Worksheet_Change Routine, then gives me an error on the GoalSeek line. Any thoughts on how to do this? Thanks in advance. And I'll be sure to check yes to answers. -- Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
"IF" with no "false" action - Using Text | Excel Discussion (Misc queries) | |||
"IF" with no "false" action - Using Text | Excel Discussion (Misc queries) | |||
Worksheet_Change Event "Circular Reference" | Excel Programming |