在Excel 2003中，将具有数据列的vba行转换为多行的列 [英] Convert through vba row with columns of data into column with multiple rows in Excel 2003
问题描述
我有一行数据如下
（项目代码）...（Milestone1）（Date1）（Milestone2）（Date2）...（里程碑10）新新p新新新新旗新新新新旗200新新新新旗新新旗200新新新新旗新新旗200新新新新旗200新新新新旗200新新新新旗新1992 200新新新新旗新1992 200新新新新旗新1992 200新新新旗新1992 200新新新新旗新1992： 我有一个非规范化的数据集，其中数据空间可能是逐行的也可能不是空的。
ie
1234567 start 12 / 12/2012 build 12/01/2013 ...完成01/02/2014
1234568计划14/12/2012合同02/02/2013 ... NULL NULL
将成为
1234567 header33 start 12 / 12/2012
1234567 header34 build 12/01/2013
...
1234567 header51 finish 01/02/2014
1234568 header33 plan 14/12/2012
1234568 header34 contract 02/02/2013
我有大约一百行，包含名称为10个里程碑和10个关联日期，并需要拆分这些，所以我有多行列出里程碑标题/里程碑名称/日期每个项目代码....如果里程碑名称和数据是blanc可以跳过记录...
在那里sa simular post 转换行数据列到Excel 2007中的多行列，但不知道如何更改以将其应用于上述方案...
任何帮助将不胜感激
谢谢
好的，这个代码真的不是太先进，这有点乏味我为你设计了一个应该完成你所需要的。它充满了评论，您可以使用它来帮助学习代码（如果您想要再次自己做）。祝你好运！
Sub normalizeData（）
Dim i As Integer，j As Integer，k As整数
Dim r As Integer
Dim bReport As Workbook，Report As Worksheet，Report2 As Worksheet
Dim m（0到9）As Integer'这为您的里程碑$ b $设置变量新评新新新旗新新新新新旗新新旗新新旗新旗旗新新旗人次：200新200新新新新旗新新旗新新旗新新旗新新旗旗哨旗新新旗新新旗新新旗旗哨旗新新新新新新新旗新新旗新新旗新新旗新旗200 200 b $ b'有两种方法可以识别子程序的里程碑：要么有一些标识符可以在里程碑单元格的值内搜索_
;或明确定义它们。我要做后者，因为我不知道_
那些细胞可能是什么。
m（0）= 33'每个里程碑的列号
m（1）= 35
m（2）= 37
m（3）= 39
m（4）= 41
m（5）= 43
m（6）= 45
m（7）= 47
m（8）= 49
m（9）= 51
r = Report.UsedRange.Rows.Count'获取最后一行号码并将其分配给_
短变量（仅仅为了使事情更容易）。
Report2.Cells（1，1）.Value =规范化数据放置标题行可以让我们使用已使用的范围，而不必使用_
来担心识别第一次迭代。如果你曾经尝试过这个_
，你可能知道我所指的是什么。
与Report2.Range（A1：D1）'只是makin它purdy
.Merge
.HorizontalAlignment = xlCenter
.Interior.Color = RGB（0 ，20，99）
.Font.Color = RGB（224,238,255）
.Font.Bold = True
.Font.Size = 14
结束
对于i = 2至r
对于j = 0至9
如果Report.Cells（i，m（j））。值& 和_
Report.Cells（i，m（j））。值<> Null然后'我不知道空值是空值还是空字符串。
mileString = Report.Cells（i，m（j））。值
dateString = Report.Cells（i，m（j）+ 1）.Value
k = Report2.UsedRange.Rows .Count + 1
Report2.Cells（k，1）.Value = Report.Cells（i，1）.Value
Report2.Cells（k，2）.Value = Report.Cells（1， m（j））。值'假设你的头在第一行
Report2.Cells（k，3）.Value = mileString
Report2.Cells（k，4）.Value = dateString
Report2.Cells（k，4）.NumberFormat =MM / dd / yyyy
End If
Next j
Next i
For i = 2 To Report2.UsedRange.Rows.Count
如果我的Mod 2 = 0然后
Report2.Range（A& i&D& i）.Interior.Color = RGB 227,235,252）'交替行颜色以便于阅读。
End If
Next i
'**************这部分添加了边框。 ************
使用Report2.Range（A1：D& Report2.UsedRange.Rows.Count）$ b $ X-454545454545 X-45454545 X- 20045 X-454545 X-45454545 X- 20045 X-454545 X- X-454545454545 X-45454545 X-454545 X-454545 X-454545 X-454545 X-454545 X-454545 X-454545 X-454545 X- ************************************************** ************************
End Sub
PS：当您运行宏时，请确保您处于非规范化工作表。它被设计为将该表作为活动参考，并将归一化数据放入新的工作表。
另请注意，我使用一个数组来标识里程碑，但是确保您可以轻松地使用 For ... Next 循环使用步骤2增量来提高可扩展性。例如，在这种情况下，您的for循环的开始将看起来像对于j = 33到51步骤2 。这是假设你所有的里程碑都是两列。
I looked through simular topic on this subject but got stuck, I need some help of VBA experts in excel 2003
I have a row of data as follows
(Project Code) ...(Milestone1) (Date1) (Milestone2) (Date2) ... (Milestone 10)(Date3)
ColumnA ... ColumnAG ColumnAH ColumnAI ColumnAJ ... ColumnAY ColumnAZ
Header1 Header33 Header34 Header35 Header36 ... Header51 Header52
rowkey datavalue1 datavalue33 datavalue34 datavalue35 datavalue36 ... datavalue51
I have a denormalized data set where the datavalues may or may not be empty on a row-by-row basis. I need to normalize them.
ie
1234567 start 12/12/2012 build 12/01/2013 ... finish 01/02/2014
1234568 plan 14/12/2012 contract 02/02/2013 ... NULL NULL
would become
1234567 header33 start 12/12/2012
1234567 header34 build 12/01/2013
...
1234567 header51 finish 01/02/2014
1234568 header33 plan 14/12/2012
1234568 header34 contract 02/02/2013
I have about hundred of rows, containing project data with names of 10 milestones and 10 associated dates, and need to split these up so I have multiple rows listing the milestone header / milestone name / date per project code.... If the milestone name and data is blanc the record can be skipped...
There is a simular post Convert row with columns of data into column with multiple rows in Excel 2007 , but don`t know how to change to apply this to the above scenario...
Any help would be appreciated Thanks
Okay, the code for this really isn't too advanced, but it is a little tedious. I designed this one for you that should accomplish what you need. It is filled with commentary you can use to help learn the code as well (in case you want to do this again on your own). Good luck!
Sub normalizeData()
Dim i As Integer, j As Integer, k As Integer
Dim r As Integer
Dim bReport As Workbook, Report As Worksheet, Report2 As Worksheet
Dim m(0 To 9) As Integer 'This sets up the variables for your milestones
Dim mileString As String, dateString As String
Set Report = Excel.ActiveSheet
Set bReport = Report.Parent
Set Report2 = bReport.Worksheets.Add
'There are two ways to identify your milestones to the subroutine: either have some kind of identifier to search _
for within the value of the milestone cell; or define them explicitly. I'm going to do the latter since I don't know _
what might be in those cells.
m(0) = 33 'The column number for each milestone
m(1) = 35
m(2) = 37
m(3) = 39
m(4) = 41
m(5) = 43
m(6) = 45
m(7) = 47
m(8) = 49
m(9) = 51
r = Report.UsedRange.Rows.Count 'Get the last row number and assign it to a _
short variable (just to make things easier).
Report2.Cells(1, 1).Value = "Normalized Data" 'Placing a header row allows us to use used range without having _
to worry about identifying the first iteration. If you've ever tried this _
by yourself you probably know what I'm referring to.
With Report2.Range("A1:D1") 'Just makin it purdy
.Merge
.HorizontalAlignment = xlCenter
.Interior.Color = RGB(0, 20, 99)
.Font.Color = RGB(224, 238, 255)
.Font.Bold = True
.Font.Size = 14
End With
For i = 2 To r
For j = 0 To 9
If Report.Cells(i, m(j)).Value <> "" And _
Report.Cells(i, m(j)).Value <> "Null" Then 'I'm not sure if the empty values will be null or blank strings.
mileString = Report.Cells(i, m(j)).Value
dateString = Report.Cells(i, m(j) + 1).Value
k = Report2.UsedRange.Rows.Count + 1
Report2.Cells(k, 1).Value = Report.Cells(i, 1).Value
Report2.Cells(k, 2).Value = Report.Cells(1, m(j)).Value 'Assuming your header is on the first row
Report2.Cells(k, 3).Value = mileString
Report2.Cells(k, 4).Value = dateString
Report2.Cells(k, 4).NumberFormat = "MM/dd/yyyy"
End If
Next j
Next i
For i = 2 To Report2.UsedRange.Rows.Count
If i Mod 2 = 0 Then
Report2.Range("A" & i & ":D" & i).Interior.Color = RGB(227, 235, 252) 'Alternating row color for easier reading.
End If
Next i
'************** This part adds borders. Omit this block if you don't want them.************
With Report2.Range("A1:D" & Report2.UsedRange.Rows.Count)
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlLeft).Weight = xlThin
.Borders(xlRight).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
End With
'******************************************************************************************
End Sub
PS: make sure you are on the denormalized worksheet when you run the macro. It is designed to reference that sheet as the active one and place the normalized data into a new worksheet.
Also note that I used an array to identify the milestones, but I'm sure you could just as easily use a For...Next loop with a Step 2 increment for improved scalability. For example, in this case, the beginning of your for loop would look like For j = 33 to 51 Step 2. This is assuming all your milestones are two columns apart.
这篇关于在Excel 2003中，将具有数据列的vba行转换为多行的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
