excel GoalSeek循环通过列

iaqfqrcu 于 2023-03-13 发布在 Go

关注(0)|答案(2)|浏览(167)

我尝试遍历列，并对所需的每个单元格使用GoalSeek函数。
我有一个按钮，当点击，运行'GoalSeek1'.
当我运行时，单击按钮，它会正确运行，直到For循环和I get the error：“运行时错误'13'：类型不匹配””。
数据从E列（第5列）开始。
行234是结果所在的地方，行240是目标结果所在的地方，行164是改变的结果应该所在的地方。
数据示例：

Cell E164 = 60 
Cell E234 = 7 (Formula based result) 
Cell E240 = 8

当我使用手动GoalSeek时，我将E234设置为“设置单元格”，将“至值”设置为8，将“通过更改单元格”设置为E164，E164更新为69。

按要求更新代码 *

Private Sub GoalSeek1()
    'For testing purposes only
    MsgBox "GoalSeek1"
    
    'Defining variable i
    Dim i As Integer
    'Defining column count. Cell holds a number value
    Dim controlCell As Integer
    controlCell = Range("C14")
    
        'Looping through column 5 to target of (column 5 + r)
        For i = 5 To (5 + controlCell)
            Cells(234, Columns(i)).GoalSeek Goal:=Cells(240, Columns(i)), ChangingCells:=Cells(164, Columns(i))
        Next i
    
End Sub

excel

来源：https://stackoverflow.com/questions/75649909/goalseek-looping-through-columns

2条答案

按热度按时间

h79rfbju1#

请尝试：

Private Sub GoalSeek1()
'For testing purposes only
MsgBox "GoalSeek1"

'Defining variable i
Dim i As Integer
'Defining column count. Cell holds a number value
Dim controlCell As Integer
controlCell = Range("C14").Value

'Looping through column 5 to target of (column 5 + r)
For i = 5 To (5 + controlCell)
    Cells(234, i).GoalSeek Cells(240, i).Value, Cells(164, i)
Next i

End Sub

赞(0）回复(0）举报 2023-03-13

qyuhtwio2#

从这里的帮助和进一步的在线搜索。我找到了我正在寻找的答案：

Private Sub GoalSeek1()

Dim k As Long
Dim ResultCell As Range
Dim ChangingCell As Range
Dim TargetFTE As Range

Dim controlCell As Integer
controlCell = Range("C14").Value

    For k = 5 To (5 + controlCell)
        Set ResultCell = Cells(234, k)
        Set TargetFTE = Cells(240, k)
        Set ChangingCell = Cells(164, k)
        ResultCell.GoalSeek TargetFTE, ChangingCell

    Next k

End Sub

赞(0）回复(0）举报 2023-03-13

我来回答

excel GoalSeek循环通过列

2条答案

相关问题

热门标签

最新问答