我有 1-100 行。我知道如何定位特定单元格并从中获取数据,但是当可以从 1 到 100 的任何行可以更改时,我将如何做到这一点?
假设您将任何内容放入第 A3 行。您将如何通过VBA将"已更新"写入B3行?我希望这适用于 A1-A100 行。
谢谢
将以下事件宏放在工作表代码区域中:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, Intersection As Range, Cell As Range
Set A = Range("A1:A100")
Set Intersection = Intersect(Target, A)
If Intersection Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each Cell In Intersection
Cell.Offset(0, 1).Value = "Updated"
Next Cell
Application.EnableEvents = True
End Sub
- 打开VBA编辑器
- 双击您要执行操作的工作表(工作表显示在左上方框中(
- 选择代码框上方左侧框中的工作表
- 在代码框上方的右侧框中选择更改
-
粘贴代码
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) With ThisWorkbook.Worksheets("Sheet1") If Not Intersect(Target, .Range("A1:A100")) Is Nothing Then Application.EnableEvents = False .Range("B" & Target.Row).Value = "Updated" Application.EnableEvents = True End If End With End Sub