我找到了一个上一个问题的很好的答案@angelofdev 将用户输入的数据与公式结果进行交换的问题,这里。代码运行完美,但我有点卡住了。我尝试评论该帖子,但认为这可能会更好?
我需要将其应用到一系列单元格,例如(D1:D20)
,但如果我将其输入到他的代码中,它不起作用,而且我不确定我哪里错了?
代码如下:
Private Sub Worksheet_Change(ByVal Target As Range)
' The below If statement uses Intersect to check,
' if the cell being changed is NOT cell D1 it gets ignored.
If Not Intersect(Target, Target.Worksheet.Range("D1")) Is Nothing Then
Application.EnableEvents = False 'Disables events to prevent endless loop
On Error GoTo Finalise 'Re-enable events
' The below code gets the value from cell D1,
' and stores the value to the inputVal variable.
inputVal = Range("D1").Value
' The below code does your calculation,
' and stores the value to the newValue variable.
newValue = inputVal * 0.58
'Changes the value in cell D1 to the value of the newValue variable.
Range("D1").Value = newValue
End If
Finalise:
Application.EnableEvents = True
End Sub
答案1
循环遍历单元格Intersection
:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, Intr As Range
Set Intr = Intersect(Target, Range("D1:D20"))
If Not Intr Is Nothing Then
Application.EnableEvents = False
For Each cell In Intr
cell.Value = 0.58 * cell.Value
Next cell
Application.EnableEvents = True
End If
End Sub
你不需要中间变量。如果你认为有必要,请包含错误捕捉器。