|
- Private Sub Worksheet_Change(ByVal Target As Range)
- '工作表的单元格改变触发事件
- If Target.Row = 1 And Target.Column > 1 Then
- '如果单元格的行为1且列大于1则
- Dim arr, brr, d, i&, j%, x$, zf$ '声明数组、变量、字典对象
- Set d = CreateObject("scripting.dictionary") '字典对象
- arr = Sheet1.Range("a1").CurrentRegion '数组取值范围
- brr = Range("a2:a" & Range("a65536").End(xlUp).Row) '数组
- x = Target '把触发单元格值赋给变量x
- For i = 2 To UBound(arr) '循环数组行
- For j = 2 To UBound(arr, 2) '列
- zf = arr(i, 1) & "," & arr(1, j) '第一列+第一行
- d(zf) = arr(i, j) '行列标题用字典对应该行列数值
- Next
- Next
- For i = 1 To UBound(brr) '循环数组把字典对象赋值数组
- brr(i, 1) = d(brr(i, 1) & "," & x)
- Next
- Target.Offset(1, 0).Resize(UBound(brr)) = brr '数组赋值单元格
- End If
- End Sub
复制代码 |
|