Excel VBA如果值=0，则不寻求目标
Excel VBA如果值=0，则不寻求目标
提问于 2016-10-18 15:20:21
嗨，我是vba的新手，我整天都在寻找一种让它工作的方法。无论如何，我需要我的GoalSeek宏来影响列"EW“中的所有单元格，当"EX”中的相邻单元格>0时调整EP的值，如果"EX“中相邻单元格的值= 0，则将其留空或将值更改为0。列"EP“、"EW”和"EX“都会影响彼此的值。下面是我拼凑而成的代码。我收到编译错误: Block if without End if
谢谢你的帮助
Private Sub CommandButton1_Click()
Dim StartTime As Double
Dim MinutesElapsed As String
'Remember time when macro starts
StartTime = Timer
Dim lr As Long
Dim cell As Variant
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Range("A9").Select
lr = Cells.Find("*", SearchORder:=xlByRows, SearchDirection:=xlPrevious).Row
For j = 9 To lr
If Cells(j, "EX").Value > 0 Then
Cells(j, "EP").GoalSeek Goal:=60, ChangingCell:=Cells(j, "EW")
For Each cell In Range("EW9:EW" & lr)
cell.Value = WorksheetFunction.Round(cell.Value, 0)
Next cell
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Determine how many seconds code took to run
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
'Notify user in seconds
MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
End Sub回答 2
Stack Overflow用户
发布于 2016-10-18 15:23:06
在文本之前
Application.DisplayAlerts = True
Application.ScreenUpdating = True你需要写下：
end if
next j因此，它应该看起来像这样：
Next cell
end if
next j
Application.DisplayAlerts = True
Application.ScreenUpdating = TrueStack Overflow用户
发布于 2016-10-18 15:29:01
正如错误消息所说，在If块的末尾没有End If。如果我理解你的意思，它应该在cell循环之后。您还需要放置Next j，以显示程序在何处完成j循环的迭代。
Private Sub CommandButton1_Click()
Dim StartTime As Double
Dim MinutesElapsed As String
Dim lr As Long
Dim cell As Variant
'Remember time when macro starts
StartTime = Timer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Range("A9").Select
lr = Cells.Find("*", SearchORder:=xlByRows, SearchDirection:=xlPrevious).Row
For j = 9 To lr
If Cells(j, "EX").Value > 0 Then
Cells(j, "EP").GoalSeek Goal:=60, ChangingCell:=Cells(j, "EW")
For Each cell In Range("EW9:EW" & lr)
cell.Value = WorksheetFunction.Round(cell.Value, 0)
Next cell
End If
Next j
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Determine how many seconds code took to run
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
'Notify user in seconds
MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40101947
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号