我有一个电子表格,其中列出了房间内的医疗设备(由 JSN 标识)。单个设备(JSN)在房间内可能有多个数量。我想将所有数量大于 1 的行分成具有相同数据的多行,同时将数量更改为 1 EA。以下是现有电子表格的示例(减去其他列):
Nomenclature JSN Wayfinding Rm # QTY Installed
Shelving, Solid M2090 40-179 3 5/5/15
Waste Can, Swing F2010 11-087 2 9/9/15
Stand, Mayo, Screw M8810 11-078 1 8/1/15
我需要它看起来像这样:
Nomenclature JSN Wayfinding Rm # QTY Installed
Shelving, Solid M2090 40-179 1 5/5/15
Shelving, Solid M2090 40-179 1 5/5/15
Shelving, Solid M2090 40-179 1 5/5/15
Waste Can, Swing F2010 11-087 1 9/9/15
Waste Can, Swing F2010 11-087 1 9/9/15
Stand, Mayo, Screw M8810 11-078 1 8/1/15
任何帮助都将不胜感激。请注意,我今天才发现 VBA 和宏!正在努力学习。非常感谢任何可以帮助这个努力但热切的新手的人!
答案1
假设这些数据从单元格 A1 开始,类似这样的方法应该可行
实际上 - 在这里,它们会保持秩序
Sub test()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Dim howmany As Integer
For i = lastrow To 1 Step -1
If Cells(i, 4) > 1 Then
howmany = Cells(i, 4)
For j = 1 To howmany - 1
Rows(i + 1).Insert (xlShiftDown)
Cells(i, 4) = 1
Cells(i + 1, 1) = Cells(i, 1)
Cells(i + 1, 2) = Cells(i, 2)
Cells(i + 1, 3) = Cells(i, 3)
Cells(i + 1, 4) = Cells(i, 4)
Cells(i + 1, 5) = Cells(i, 5)
Next
End If
Next
End Sub
以下将其放在最后:
Sub test()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Dim nextrow As Integer
nextrow = lastrow + 1
Dim howmany As Integer
For i = 1 To lastrow
If Cells(i, 4) > 1 Then
howmany = Cells(i, 4)
For j = 1 To howmany - 1
Cells(i, 4) = 1
Cells(nextrow, 1) = Cells(i, 1)
Cells(nextrow, 2) = Cells(i, 2)
Cells(nextrow, 3) = Cells(i, 3)
Cells(nextrow, 4) = Cells(i, 4)
Cells(nextrow, 5) = Cells(i, 5)
nextrow = nextrow + 1
Next
End If
Next
End Sub