如何使用 VBA 以特定逻辑编程拆分数据

Question

这是对我之前文章的补充这里。之前提供给我的代码运行得很好，但我在修改它并添加一些额外的逻辑时遇到了问题。

我正在 VBA 中创建一个宏来执行以下操作。我有原始订单数据，需要根据某种逻辑对其进行转换。

原始数据：

order-id      product-num     date      buyer-name  prod-name   qty-purc  sales-tax  freight  order-st
0000000000-00 10000000000000  5/29/2014 John Doe    Product 0   1         1.00       1.50     GA
0000000000-00 10000000000001  5/29/2014 John Doe    Product 1   2         1.00       1.50     GA
0000000000-00 10000000000002  5/29/2014 John Doe    Product 2   1         1.00       2.00     GA
0000000000-01 10000000000002  5/30/2014 Jane Doe    Product 2   1         0.00       0.00     PA
0000000000-01 10000000000003  5/30/2014 Jane Doe    Product 3   1         0.00       0.00     PA

期望的结果：

HDR 0000000000-00   John Doe    5/29/2014
CHG Tax 3.00
CHG Freight  5.00  
ITM 10000000000000  Product 0   1
ITM 10000000000001  Product 1   2
ITM 10000000000002  Product 2   1
HDR 0000000000-01   Jane Doe    5/30/2014
ITM 10000000000002  Product 2   1
ITM 10000000000003  Product 3   1

“CHG”行是根据以下逻辑创建的：如果订单号为 CA 或 GA，则将具有相同订单 ID 的每行的销售税和运费总额相加。如果订单号不是 CA 或 GA，则不应创建 CHG 行。

任何帮助都将不胜感激 - 如果我遗漏了任何细节，请告诉我！

Answer 1

这样就可以了，但只能使用您提供的详细信息进行测试

Sub SortMeOut()

Dim previousOrderId As String

Dim row As Integer
row = 2


previousOrderId = Worksheets("Sheet1").Range("A2").Value

Dim offset As Integer
offset = 0

Do While (True)

        If Worksheets("Sheet1").Range("A" & row).Value = "" Then
        Exit Do
        End If

    Dim isHeader As Boolean
    isHeader = True

   Dim headerRow As Integer
    headerRow = -99 ' be a duff number to test easily

    Dim totalTax As Double
    totalTax = 0

    Dim totalFreight As Double
    totalFreight = 0

    Do While (True) 'loop through all columns with a match

        If Worksheets("Sheet1").Range("A" & row).Value <> previousOrderId Then
            Exit Do
        End If

        'Be there a value for the new thingy! If so, accumulate them
        If Worksheets("Sheet1").Range("I" & row).Value = "GA" Or Worksheets("Sheet1").Range("I" & row).Value = "CA" Then
            totalTax = totalTax + Worksheets("Sheet1").Range("G" & row).Value
            totalFreight = totalFreight + Worksheets("Sheet1").Range("H" & row).Value
        End If

        If Not isHeader Then
           Worksheets("Sheet2").Range("A" & row + 1 + offset).Value = "ITM"
            Worksheets("Sheet2").Range("B" & row + 1 + offset).Value = Worksheets("Sheet1").Range("B" & row).Value ' product num
            Worksheets("Sheet2").Range("C" & row + 1 + offset).Value = Worksheets("Sheet1").Range("E" & row).Value ' product name
            Worksheets("Sheet2").Range("D" & row + 1 + offset).Value = Worksheets("Sheet1").Range("F" & row).Value ' quantity
            End If


        If isHeader Then
        headerRow = row
            Worksheets("Sheet2").Range("A" & row + offset).Value = "HDR"
            Worksheets("Sheet2").Range("B" & row + offset).Value = Worksheets("Sheet1").Range("A" & row).Value 'order id
            Worksheets("Sheet2").Range("C" & row + offset).Value = Worksheets("Sheet1").Range("D" & row).Value ' name of the dude
            Worksheets("Sheet2").Range("D" & row + offset).Value = Worksheets("Sheet1").Range("C" & row).Value ' date

            'we also have to do the first item as well...
             Worksheets("Sheet2").Range("A" & row + 1 + offset).Value = "ITM"
            Worksheets("Sheet2").Range("B" & row + 1 + offset).Value = Worksheets("Sheet1").Range("B" & row).Value ' product num
            Worksheets("Sheet2").Range("C" & row + 1 + offset).Value = Worksheets("Sheet1").Range("E" & row).Value ' product name
            Worksheets("Sheet2").Range("D" & row + 1 + offset).Value = Worksheets("Sheet1").Range("F" & row).Value ' quantity
            isHeader = False
        End If

      row = row + 1

    Loop


'at this stage we simply insert the values, or at least, we try to. If it goes wrong, then time for tea!

If totalTax > 0 Then
headerRow = headerRow + 1
offset = offset + 1
    Range("A" & headerRow).EntireRow.Insert
    Range("A" & headerRow).Value = "CHG Tax"
    Range("B" & headerRow).Value = totalTax
End If

If totalFreight > 0 Then
    headerRow = headerRow + 1
    offset = offset + 1
    Range("A" & headerRow).EntireRow.Insert
    Range("A" & headerRow).Value = "CHG Freight"
    Range("B" & headerRow).Value = totalFreight
End If

offset = offset + 1
previousOrderId = Worksheets("Sheet1").Range("A" & row).Value


Loop

End Sub

在运行 VBa 之后（尽管我承认，我遇到了一些问题（未修复），例如数量显示为日期（？！）但我已手动修复了此屏幕截图）。

Answer 2

在调整了 @DaveRook 提供的解决方案后，我终于解决了这个问题。我只是包括了我更改的部分，即打印 CHG 行的 If 循环。

If totalTax > 0 Then
    offset = offset + 1
    Worksheets("Sheet2").Range("A" & headerRow + offset).EntireRow.Insert
    Worksheets("Sheet2").Range("A" & headerRow + offset).Value = "CHG Tax"
    Worksheets("Sheet2").Range("B" & headerRow + offset).Value = totalTax
End If

If totalFreight > 0 Then
    offset = offset + 1
    Worksheets("Sheet2").Range("A" & headerRow + offset).EntireRow.Insert
    Worksheets("Sheet2").Range("A" & headerRow + offset).Value = "CHG Freight"
    Worksheets("Sheet2").Range("B" & headerRow + offset).Value = totalFreight
End If

基本上，以前的版本没有考虑值的偏移。

示例 1：

示例 2：