vba 目标查找多行 - MS-Excel 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13401165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Goal seek multiple rows - MS-Excel Macro
提问by davidb
i have 5028 rows excluding the headings and 3 columns, want to set value 1.7 in the cloumn "C" by changing the value in the column "B".
我有 5028 行不包括标题和 3 列，想通过更改列“B”中的值在“C”列中设置值 1.7。
using goal seek option it is possible only for one cell. i want to do the same for 5028 rows, please help to do the task by running some macro.
使用目标搜索选项只能针对一个单元格。我想对 5028 行做同样的事情，请通过运行一些宏来帮助完成任务。
回答by Jook
John Bustos pointed to the right idea, here is a working solution:
John Bustos 指出了正确的想法，这是一个可行的解决方案：
Public Sub Demo()
Dim rngRow As Range
For Each rngRow In UsedRange.Rows
rngRow.Cells(1, 3).GoalSeek Goal:=1.7, ChangingCell:=rngRow.Cells(1, 2)
Next rngRow
End Sub
Edit:
编辑：
Use ActiveSheet.UsedRange.Rowsinstead of UsedRange.Rows, if you intend to use this as a Macro in a Modul, not as one of a Worksheet - or any other reference to a valid Range.
如果您打算将其用作模块中的宏，而不是用作工作表之一 - 或对有效范围的任何其他引用，请使用ActiveSheet.UsedRange.Rows代替UsedRange.Rows。
For your example, you might prefer to use: Range("A2:C5028").Rowsor MySheet.Range("A2:C5028").Rows.
对于您的示例，您可能更喜欢使用:Range("A2:C5028").Rows或MySheet.Range("A2:C5028").Rows。
Edit:
编辑：
Public Sub Demo()
On Error Resume Next
Dim rngRow As Range
For Each rngRow In ActiveSheet.UsedRange.Rows
rngRow.Cells(1, 3).GoalSeek Goal:=1.7, ChangingCell:=rngRow.Cells(1, 2)
Next rngRow
End Sub
回答by John Bustos
You can create a macro which loops and then does a Range-Goal seek - http://msdn.microsoft.com/en-us/library/office/bb209907%28v=office.12%29.aspx
您可以创建一个循环的宏，然后进行范围目标搜索 - http://msdn.microsoft.com/en-us/library/office/bb209907%28v=office.12%29.aspx
回答by xcohenx
This is good code. I modified it for my purpose, which is for not just a column but multiple columns. In order to run my code, you must first select (highlight) all the cells you wish to GoalSeek (connected like in a table, not separate areas). My code:
这是很好的代码。我出于我的目的对其进行了修改，这不仅适用于一列，而且适用于多列。为了运行我的代码，您必须首先选择（突出显示）您希望 GoalSeek 的所有单元格（像表格一样连接，而不是单独的区域）。我的代码：
Sub GoalSeek_To_0()
' Macro created 15 Jun '18 by Benjamin Cohen
On Error Resume Next
Dim row_, col_ As Range
Dim i_, j_ As Integer
' i_ = 1
j_ = 1
For Each col_ In Selection.Columns
For Each row_ In Selection.Rows
row_.Cells(1, j_).GoalSeek Goal:=0, ChangingCell:=row_.Cells(1, j_).Offset(0, -1)
row_.Cells(1, j_).Value = 1
Next row_
j_ = j_ + 1
Next col_
End Sub
