问题描述
当工作表中的任何单元格发生更改时,我都希望跨几行应用目标搜索.我想将其从第7行应用到第11行.我遇到的第一个问题是,每次运行此命令时excel都会崩溃.我才刚刚开始学习VBA,因此对您的帮助非常感激.谢谢!
I want to apply goal seek across several rows when there is a change to any cell in the work sheet. I want to apply this from row 7 to row 11. The first problem I have is that excel is crashing each time I run this. I am just starting to learn VBA so any help is much apreciated. Thank you!
我的代码如下:
Option Explicit
Private Sub Worksheet_Calculate()
CheckGoalSeek
End Sub
Private Sub CheckGoalSeek()
Range("T7").GoalSeek Goal:=0, ChangingCell:=Range("V7")
End Sub
推荐答案
您似乎正在触发一个无限循环:工作表计算->目标搜索计算->工作表计算-> ...
You appear to be triggering an infinite loop: worksheet calculation -> goal seek calculation -> worksheet calculation -> ...
一种选择是更改触发目标搜索的事件.
One option is to change the event that triggers the goal seek.
我建议使用Worksheet_Change事件.除了子声明Private Sub Worksheet_Change(ByVal Target As Range)
.
I would recommend the Worksheet_Change event. The event code would be the same except for the sub declaration, which would be Private Sub Worksheet_Change(ByVal Target As Range)
.
一个简单的For
循环将在不同的行上执行目标搜索:
A simple For
loop will perform the Goal Seek on the different rows:
Option Explicit
Private Sub CheckGoalSeek()
Dim i as Long
For i = 7 to 11
Range("T"& i).GoalSeek Goal:=0, ChangingCell:=Range("V"& i)
Next
End Sub
这篇关于自动目标搜寻超过单元格的范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!