Excel VBA Worksheet_Change for a Range of values

Question

我有VBA问题，我需要使用工作表更改事件从AI28到AI30拾取单元格值，并将它们移到V28到V30。这就是我到目前为止所做的

Private Sub Worksheet_Change(ByVal Target As Range)
If IsNumeric(Target) And Not (Target = "") Then
If Target.Address = Range("AI28:AI30").Address Then
Range("V28:V30").Value = Range("AH28:AH30").Value

Else
If Target.Cells.Value <> Empty Then Exit Sub
Exit Sub

End If
End If

End Sub

它只适用于一个范围，例如AI28和V28，所以我缺少什么？循环什么的？

Answer 1

使用循环和Intersect:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Me.Range("AI28:AI30"))
If rng Is Nothing Then Exit Sub
On Error GoTo SafeExit
Application.EnableEvents = False
Dim cell As Range
For Each cell In rng
If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
Me.Range("V" & cell.Row).Value = cell.Value
End If
Next
SafeExit:
Application.EnableEvents = True
End Sub