excel GoalSeek循环遍历行

sshcrbum  于 2023-05-19  发布在  Go
关注(0)|答案(1)|浏览(261)

我试着在行中循环。我需要循环遍历的范围位于B列(设置单元格)和G列(更改单元格),并从第18行开始。循环需要遍历一定数量的行,其数量在单元格“F9”中以整数形式找到,即细胞F9可以包含22。“To”值将始终为0。
本质上,B列中的数字需要通过重新计算G列中的%来达到0.0。

需要注意的是,行数是动态的,可能会超过我上传的图像中的行数。
我使用了下面的方法来实现列上的GoalSeek,其中“To Value”也是动态的。我不知道如何将它转换为我目前的需求。

Private Sub GoalSeek()
    
    Dim k As Long
    Dim ResultCell As Range
    Dim ChangingCell As Range
    Dim TargetFTE As Range
    
    Dim controlCell As Integer
    controlCell = Range("C15").Value
    
    For k = 5 To (5 + controlCell)

        Set ResultCell = Cells(167, k)
        Set TargetFTE = Cells(173, k)
        Set ChangingCell = Cells(125, k)
        ResultCell.GoalSeek TargetFTE, ChangingCell

    Next k
  
End Sub
kkbh8khc

kkbh8khc1#

找到了一个可行的解决方案。

Sub GoalSeek_productivity()
'Defining variable k
Dim k As Integer
'Defining number of loops
Dim controlCell As Integer
controlCell = Range("F9").Value
'We are looping through each row in our table based on value in the controlCell minus 9 as the last number always needs to be 100%
    For k = 25 To (25 + controlCell - 9)
        'Below is how we replicate the Goal Seek functionality via VBA
        Range("B" & k).GoalSeek Goal:=0, ChangingCell:=Range("G" & k)
    'Go to next iteration
    Next k    
End Sub

相关问题