时间戳

首页 > 解决方案 > VBA 中的目标搜索

excel - VBA 中的目标搜索

问题描述

我有一系列迭代需要将目标值执行为“0”。但是经过一系列的迭代，goalseek 似乎并没有正确执行它的任务。

我附上了非零的目标目标值。

此处附上错误值说明

Sub BP_design()
i = 1
Sheets("Reactions").Activate
With Sheets("Reactions")
    Do While .Cells(i + 1, 3) <> ""

        ND = .Cells(i + 1, 11)
        LC = .Cells(i + 1, 12)
        FX = Abs(.Cells(i + 1, 13))
        Fy = .Cells(i + 1, 14)
        FZ = Abs(.Cells(i + 1, 15))
        Mx = Abs(.Cells(i + 1, 16))
        My = Abs(.Cells(i + 1, 17))
        Mz = Abs(.Cells(i + 1, 18))
        ''''''''''''''''''''''''''''''''
        Sheets("Base_Plate").Cells(62, 2) = ND
        Sheets("Base_Plate").Cells(59, 8) = LC
        Sheets("Base_Plate").Cells(62, 5) = FX
        Sheets("Base_Plate").Cells(62, 8) = Fy
        Sheets("Base_Plate").Cells(62, 11) = FZ
        Sheets("Base_Plate").Cells(62, 14) = Mx
        Sheets("Base_Plate").Cells(62, 17) = My
        Sheets("Base_Plate").Cells(62, 20) = Mz
        ''''''''''''''''''''''''''''''''
        .Cells(i + 1, 20) = Sheets("Base_Plate").Cells(95, 25) 'F
        .Cells(i + 1, 21) = Sheets("Base_Plate").Cells(96, 25) 'e
        .Cells(i + 1, 22) = Sheets("Base_Plate").Cells(97, 25) 'K1
        .Cells(i + 1, 23) = Sheets("Base_Plate").Cells(98, 25) 'K2
        .Cells(i + 1, 24) = Sheets("Base_Plate").Cells(99, 25) 'K3
        
        ''''''''''''''''''''''''''''''''
        
        Sheets("Base_Plate").Cells(104, 23).GoalSeek Goal:=0, ChangingCell:=Sheets("Base_Plate").Cells(105, 5)

        .Cells(i + 1, 26) = Sheets("Base_Plate").Cells(105, 5)
        .Cells(i + 1, 25) = Sheets("Base_Plate").Cells(104, 23) '0
        ''''''''''''''''''''''''''''''''
        .Cells(i + 1, 27) = Sheets("Base_Plate").Cells(111, 23)
        .Cells(i + 1, 28) = Sheets("Base_Plate").Cells(114, 23)
        .Cells(i + 1, 30) = Sheets("Base_Plate").Cells(116, 23)
        ''''''''''''''''''''''''''''''''
        .Cells(i + 1, 32) = Sheets("Base_Plate").Cells(118, 23)
        .Cells(i + 1, 33) = Sheets("Base_Plate").Cells(120, 23)
        .Cells(i + 1, 34) = Sheets("Base_Plate").Cells(123, 23)
        .Cells(i + 1, 35) = Sheets("Base_Plate").Cells(130, 15)
        ''''''''''''''''''''''''''''''''
              
        i = i + 1


 Loop
End With
End Sub

标签： excelvbaloops

解决方案

目标搜索的准确性取决于执行了多少计算才能到达目标。尝试在选项 > 公式中提高最大值

例如https://superuser.com/questions/688691/why-is-that-sometimes-the-excel-goal-seek-doesnt-stop-exactly-at-zero

或在 VBA 中使用Application.MaxIterations属性：https ://docs.microsoft.com/en-us/office/vba/api/excel.application.maxiterations

推荐阅读