每种材料都有多行(行数会有所不同),制造商和制造商零件编号的值也不同。我需要将其转换为每种材料一行,所有制造商和制造零件编号都在同一行。
由于我无法附加图像,因此下面提供了示例:
现有数据
Material1 Mfgr1 MPN11
Mfgr2 MPN12
Material2 Mfgr2 MPN21
Mfgr3 MPN22
Mfgr4 MPN23
所需数据
Material1 Mfgr1 MPN11 Mfgr2 MPN12
Material2 Mfgr2 MPN21 Mfgr3 MPN22 Mfgr4 MPN23
答案1
这段代码可以达到这个目的:
Sub materialsinglerow()
Dim wkb As Workbook
Dim wks, wks1 As Worksheet
Set wkb = ThisWorkbook
Set wks = wkb.Sheets(1)
Set wks1 = wkb.Sheets(2)
continue = True
i = 1
j = 0
k = 2
emptycount = 0
lastmaterial = ""
While continue = True
material = wks.Cells(i, 1)
mfgr = wks.Cells(i, 2)
mpn = wks.Cells(i, 3)
If material <> "" Then
j = j + 1
k = 2
wks1.Cells(j, 1) = material
lastmaterial = material
End If
If mfgr <> "" Then
wks1.Cells(j, k) = mfgr
k = k + 1
wks1.Cells(j, k) = mpn
k = k + 1
Else
emptycount = emptycount + 1
End If
If emptycount > 10 Then
continue = False
End If
i = i + 1
Wend
它从工作表1并将结果写入工作表2假设原始数据为列A, B and C
。
当列中空单元格超过 10 个时,它就会停止C
。