VBA将行转换为列 [英] VBA convert rows to columns

问题描述

我尝试从TABLE1转换为TABLE2

I try to make convertion from TABLE1 to TABLE2

使用vba

code should go here, but when i paste it, i got erro that I have to much    code

因此，我将链接添加到TXT文档 vba代码

so, i add link to TXT document vba code

但是无法弄清楚如何将YEAR数据从TABLE1 B1:G1移到TABLE2的B列.

but can't figure out how to move the YEAR data from TABLE1 B1:G1 to column B of TABLE2.

推荐答案

假设您使用示例中所示的安装程序，那么这对我有用:

Assuming you use the Setup as shown in your example, this worked for me:

Sub tt()

Dim ws As Worksheet, nws As Worksheet
Dim i As Long, j As Long, c As Long
Set ws = ActiveSheet
Set nws = Worksheets.Add
i = 0
c = 0

nws.Range("A1").Value = "Country"
nws.Range("B1").Value = "Year"
nws.Range("C1").Value = "Value"
nws.Range("D1").Value = "Text"
Do While ws.Cells(2 + i, 1).Value <> ""
    j = 0
    Do While ws.Cells(1, 2 + j).Value <> ""

        nws.Cells(2 + c, 1).Value = ws.Cells(2 + i, 1).Value
        nws.Cells(2 + c, 2).Value = ws.Cells(1, 2 + j).Value
        nws.Cells(2 + c, 3).Value = ws.Cells(2 + i, 2 + j).Value
        nws.Cells(2 + c, 4).Value = "YES"
        c = c + 1
        j = j + 1
    Loop
    i = i + 1
Loop

End Sub

---

对于非VBa解决方案，您可以使用本机Worksheets-Function来执行此操作，例如:

---

For a non-VBa Solution, you can do this with the native Worksheets-Functions like such:

假设您具有图片中的设置，请将这些公式分别放在A9，B9和C9中，然后适当地向下拖动:

Assuming you have the setup like in the picture put these Formulas in A9, B9 and C9 and drag down as suited:

对于A9；

=INDIRECT(CONCATENATE("A",ROUNDDOWN((ROW(1:1)-1)/COUNTA($B$2:$G$2),0)+3))

对于B9:

=INDEX($B$2:$G$2,MOD((ROW(1:1)-1),COUNTA($B$2:$G$2))+1)

对于C9:

=INDEX($A$3:$G$5,MATCH(E9,$A$3:$A$5,0),MOD(ROW(1:1)-1,COUNTA($B$2:$G$2))+2)

这篇关于VBA将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！