我有想要转置的数据,这应该很容易。但是,我需要将多行分组转置。每个组的最后一行都有特定的文本,所以我不知道是否有办法创建一个搜索文本而不是范围的范围?(这可行吗?=if(isnumber(search(“TEXT”,A1))
。我尝试创建一个宏,但显然我需要代码来将数据在电子表格中向下转置,而不是一遍又一遍地转置相同的数据。任何帮助都非常感谢!
这是我需要的视觉效果(因为我无法格式化它,所以看起来很有趣,而且由于我是新手,我无法嵌入它,所以我包含了行/列的外观/应该是什么样子的视觉链接:
由此:
Column A
Row A1
Row A2
Row A3
Row A4
Row A5
Row A6
Row A7
Row A8
Row A9
Row A10
Row A11
Row A12
Row A13
对此:
Col A Col B Col C Col D Col E
Row A1 Row A2 Row A3 Row A4
Row A5 Row A6 Row A7 Row A8 Row A9
Row A10 Row A11 Row A12 Row A13
这是我找到的循环代码:
Sub Test1()
'UpdatebyExtendoffice20161222
Dim x As Integer
Application.ScreenUpdating = False
' Set numrows = number of rows of data.
NumRows = Range("A1", Range("A8”).End(xlDown)).Rows.Count
' Select cell a1.
Range("A1").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
' Insert your code here.
' Selects cell down 7 row from active cell.
ActiveCell.Offset(7, 0).Select
Next
Application.ScreenUpdating = True
End Sub
答案1
假设我们从以下开始:
我们希望将其重新组织成行,每行的最后一项是新的.此代码:
Sub ReOrg()
Dim i As Long, j As Long, N As Long, K As Long
Dim kk As Long
i = 1
j = 2
K = Cells(Rows.Count, "A").End(xlUp).Row
For kk = 1 To K
Cells(i, j).Value = Cells(kk, 1).Value
j = j + 1
If Cells(kk, 1).Value = "New" Then
i = i + 1
j = 2
End If
Next kk
End Sub
将产生: