|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 suiyuanban 于 2022-6-1 13:04 编辑
Sub t()
Dim arr, brr
Dim wb As Workbook
Application.ScreenUpdating = False
arr = Sheet1.Range("d14:q" & Sheet1.Range("d65536").End(3).Row)
For i = 1 To UBound(arr)
If arr(i, 14) <> "" Then j = j + 1
Next
ReDim brr(1 To UBound(arr) + j, 1 To 24)
k = UBound(brr)
For i = UBound(arr) To 1 Step -1
If arr(i, 14) = "" Then
'零件名,物料号,数量,供应商代码,供应商
brr(k, 1) = arr(i, 4): brr(k, 8) = arr(i, 2): brr(k, 11) = arr(i, 6): brr(k, 19) = arr(i, 7): brr(k, 24) = arr(i, 8)
k = k - 1
Else
'零件名,物料号,数量,供应商代码,供应商
brr(k, 1) = arr(i, 4): brr(k, 8) = arr(i, 2): brr(k, 11) = arr(i, 6): brr(k, 19) = arr(i, 7): brr(k, 24) = arr(i, 8)
'零件名,物料号,数量
brr(k - 1, 1) = Split(arr(i, 14), Chr(10), 2)(0): brr(k - 1, 8) = Split(arr(i, 14), Chr(10), 2)(1): brr(k - 1, 11) = arr(i, 6)
k = k - 2
End If
Next
fname = ThisWorkbook.Path & "\总数据.xlsm"
Set wb = Workbooks.Open(fname)
Range("g15:ah" & Range("k65536").End(3).Row).ClearContents
Range("k15").Resize(UBound(brr), 24) = brr
For i = 1 To UBound(brr)
If brr(i, 19) = "" Then
With Range(Cells((i + 14), 7), Cells((i + 14), 35))
.Font.ColorIndex = 3
.Font.Size = 10
.Font.Bold = True
.Interior.ColorIndex = 27
End With
With Range("a15:ai" & UBound(brr) + 14)
.Font.Size = 10
.Borders.LineStyle = 1
End With
End If
Next
wb.Save
wb.Close
Set wb = Nothing
Application.ScreenUpdating = True
End Sub
|
|