- Sub demo1()
- Dim arr
- Dim i&, j&, k&
- With Worksheets("数据区")
- arr = .Range(.Cells(Rows.Count, 1).End(xlUp), .Range("s1")).Value
- End With
- Dim result()
- ReDim result(1 To UBound(arr) * (UBound(arr, 2) - 1), 1 To 4)
- '列
- For i = 3 To UBound(arr, 2)
- '行
- For j = 2 To UBound(arr)
- k = k + 1
- result(k, 1) = arr(j, 1)
- result(k, 2) = arr(j, 2)
- result(k, 3) = arr(j, i)
- result(k, 4) = arr(1, i)
- Next
- k = k + 1
- result(k, 1) = "'/"
- Next
- Sheets.Add
- Range("a2").Resize(k - 1, 4).Value = result
- Range("a1").Resize(, 4).Value = Array("M", "ID", "元素值", "元素代号")
- End Sub
复制代码 |