英文:
Automatically update cells
问题
我目前在我的工作表上有这段代码：
```vba
Private Sub Worksheet_Change(ByVal Target As Range)
' 确保在退出此子过程时始终启用事件，即使出现错误
On Error GoTo errExit
Application.EnableEvents = False
Dim SpotRate As Double
SpotRate = Me.Range("H2").Value
If Target.Address = "$C$3" Then
If Target.Value <> "" Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
ElseIf Target.Address = "$E$3" Then
If Target.Value <> "" Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
End If
errExit:
Application.EnableEvents = True
End Sub
C2单元格中有一个下拉菜单，其中包含汇率信息。
当我在C3或E3中输入值时，代码按照要求工作。但是，如果我更改C2单元格中的值（这会更改H2中的汇率），这些值不会自动更新到C3或E3。有没有办法可以更改这个，使C3中的值保持不变，但根据新的汇率自动更新E3？谢谢。
<details>
<summary>英文:</summary>
I currently have this code for my worksheet:
```vba
Private Sub Worksheet_Change(ByVal Target As Range)
' ensure events are always enabled when we exit this Sub, even if we have an error
On Error GoTo errExit
Application.EnableEvents = False
Dim SpotRate As Double
SpotRate = Me.Range("H2").Value
If Target.Address = "$C$3" Then
If Target.Value <> "" Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
ElseIf Target.Address = "$E$3" Then
If Target.Value <> "" Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
End If
errExit:
Application.EnableEvents = True
End Sub
There is a pair a dropdown in cell C2 that contains information for the spot rate.
When I input the values into C3 or E3 the code works as required. However, if I change the value in cell C2 (which changes the spot rate in H2), these values don't automatically update in C3 or E3. Is there a way I can change this so that the value in C3 is held constant but E3 is automatically updated according to the new spot rate? Thanks.
答案1
得分: 1
尝试这个：
私有子工作表_更改(ByVal目标作为范围)
Dim c As Range, v, SpotRate As Double
如果目标.CountLarge > 1然后退出子 '仅处理单元格更改
如果有错误则转到 errExit
SpotRate = Me.Range("H2").Value
选择情况目标地址(False，False)
案例 "C2"
'通过事件处理程序触发更新
Me.Range("C3").Value = Me.Range("C3").Value
Me.Range("E3").Value = Me.Range("E3").Value
案例 "C3"
Application.EnableEvents = False
如果目标.Value <> "" 然后
范围("E3").Value = 目标.Value * SpotRate
其他
范围("E3").ClearContents
结束如果
案例 "E3"
Application.EnableEvents = False
如果目标.Value <> "" 然后
范围("C3").Value = 目标.Value / SpotRate
其他
范围("C3").ClearContents
结束如果
结束选择
errExit:
如果错误号 <> 0然后调试打印错误说明
Application.EnableEvents = True
结束子
请注意，我已经将代码部分保持不变，只对注释进行了中文翻译。
英文:
Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, v, SpotRate As Double
If Target.CountLarge > 1 Then Exit Sub 'only handling single-cell changes
On Error GoTo errExit
SpotRate = Me.Range("H2").Value
Select Case Target.Address(False, False)
Case "C2"
'trigger updates via event handler
Me.Range("C3").Value = Me.Range("C3").Value
Me.Range("E3").Value = Me.Range("E3").Value
Case "C3"
Application.EnableEvents = False
If Target.Value <> "" Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
Case "E3"
Application.EnableEvents = False
If Target.Value <> "" Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
End Select
errExit:
If Err.number <> 0 Then Debug.Print Err.Description
Application.EnableEvents = True
End Sub
答案2
得分: 0
如果目标地址是"$H$2"，你需要一个等价的代码块来执行计算。
假设我对你尝试实现的目标理解正确，这段代码应该对你有用：
Private Sub Worksheet_Change(ByVal Target As Range)
' 确保在退出此子程序时始终启用事件，即使发生错误也是如此
On Error GoTo errExit
Application.EnableEvents = False
Dim SpotRate As Double
SpotRate = Me.Range("H2").Value
' 如果H2不是一个数字
If Not IsNumeric(SpotRate) Then
Range("E3").ClearContents
Range("C3").ClearContents
Exit Sub
End If
'E3 改变
If Target.Address = "$E$3" Then
' 调整E3
If IsNumeric(Range("E3").Value) Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
'C3 改变
ElseIf Target.Address = "$C$3" Then
' 调整C3
If IsNumeric(Me.Range("C3").Value) Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
'C2 改变
ElseIf Target.Address = "$C$2" Then
If IsNumeric(Me.Range("C2").Value) Then
' 调整E3
If IsNumeric(Range("E3").Value) Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
' 调整C3
If IsNumeric(Me.Range("C3").Value) Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
Else
' C2 不是一个数字
Range("E3").ClearContents
Range("C3").ClearContents
End If
End If
errExit:
Application.EnableEvents = True
End Sub
英文:
You have blocks of code that perform some actions when the Target (range that changed, triggering the Worksheet_Change event) address is "$C$3" and "$E$3".
If Target.Address = "$C$3" Then
ElseIf Target.Address = "$E$3" Then
You need an equivalent block that will perform the calculation if Target.Address = "$H$2".
Assuming my understanding of what you're trying to achieve is correct, this should work for you:
Private Sub Worksheet_Change(ByVal Target As Range)
' ensure events are always enabled when we exit this Sub, even if we have an error
On Error GoTo errExit
Application.EnableEvents = False
Dim SpotRate As Double
SpotRate = Me.Range("H2").Value
'if H2 is not a number
If Not IsNumeric(SpotRate) Then
Range("E3").ClearContents
Range("C3").ClearContents
Exit Sub
End If
'E3 changed
If Target.Address = "$E$3" Then
'adjust E3
If IsNumeric(Range("E3").Value) Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
'C3 changed
ElseIf Target.Address = "$C$3" Then
'adjust C3
If IsNumeric(Me.Range("C3").Value) Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
'C2 changed
ElseIf Target.Address = "$C$2" Then
If IsNumeric(Me.Range("C2").Value) Then
'adjust E3
If IsNumeric(Range("E3").Value) Then
Range("E3").Value = Target.Value * SpotRate
Else
Range("E3").ClearContents
End If
'adjust C3
If IsNumeric(Me.Range("C3").Value) Then
Range("C3").Value = Target.Value / SpotRate
Else
Range("C3").ClearContents
End If
Else
'C2 is not a number
Range("E3").ClearContents
Range("C3").ClearContents
End If
End If
errExit:
Application.EnableEvents = True
End Sub
EDIT: Sorry, misuderstood the question at first, corrected my comment to include the right solution.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论