想把EXCEL 各欄逗號前的項目以10個為單位合在一起 VBA該如何寫好? 附圖

excel excel vba

mmote895623 2021-04-29 15:34:24 ‧ 1052 瀏覽

分享至

上圖1->2->3是手動完成的過程第三張是想要的模式有辦法用VBA寫出來嗎?

japhenchen iT邦超人 1 級 ‧ 2021-04-29 16:04:56 檢舉

可以不用VBA解答嗎？

個人真的不喜歡VBA..

mmote895623 iT邦新手 5 級 ‧ 2021-04-29 17:10:36 檢舉

也可以啊我以為只能用VBA才可以辦到

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2021-04-30 15:04:05

最佳解答

1.在 Sheet1 準備好資料
2.執行 MainLoop
3.結果放在 Sheet2

Sub MainLoop()
    bFirst = True
    R = 2
    
    savedItem = ""
    savedQuantity = ""
    savedReference = ""
    savedPart = ""
    savedFootprint = ""
    
    Do While Sheets("Sheet1").Cells(R, 3) <> ""
                
        If bFirst = True Then
            savedItem = Sheets("Sheet1").Cells(R, 1)
            savedQuantity = Sheets("Sheet1").Cells(R, 2)
            savedReference = Sheets("Sheet1").Cells(R, 3)
            savedPart = Sheets("Sheet1").Cells(R, 4)
            savedFootprint = Sheets("Sheet1").Cells(R, 5)
            
            bFirst = False
        Else
            If Sheets("Sheet1").Cells(R, 1) = "" And Sheets("Sheet1").Cells(R, 2) = "" Then
                savedReference = savedReference & Sheets("Sheet1").Cells(R, 3)
            Else
                Call AddRowSheet2(savedItem, savedQuantity, savedReference, savedPart, savedFootprint)
                
                savedItem = Sheets("Sheet1").Cells(R, 1)
                savedQuantity = Sheets("Sheet1").Cells(R, 2)
                savedReference = Sheets("Sheet1").Cells(R, 3)
                savedPart = Sheets("Sheet1").Cells(R, 4)
                savedFootprint = Sheets("Sheet1").Cells(R, 5)
            End If
        End If
                
        R = R + 1
    Loop
    
    Call AddRowSheet2(savedItem, savedQuantity, savedReference, savedPart, savedFootprint)
End Sub
Sub AddRowSheet2(ByVal pItem, ByVal pQuantity, ByVal pReference, ByVal pPart, ByVal pFootprint)
    
    arrRef = Split(pReference, ",")
    
    For nI = 0 To UBound(arrRef) Step 10
        sRef = ""
        For nJ = nI To WorksheetFunction.Min(nI + 9, UBound(arrRef))
            sRef = sRef & arrRef(nJ) & IIf(nJ = UBound(arrRef), "", ",")
        Next nJ
        
        R = Range("Sheet2!C65536").End(xlUp).Row + 1
        
        If nI = 0 Then
            Sheets("Sheet2").Cells(R, 1) = pItem
            Sheets("Sheet2").Cells(R, 2) = pQuantity
            Sheets("Sheet2").Cells(R, 4) = pPart
            Sheets("Sheet2").Cells(R, 5) = pFootprint
        End If
        Sheets("Sheet2").Cells(R, 3) = sRef
    Next nI
    
End Sub