excel - VBA 代码相互冲突

Question

您能否告知我如何修复以下代码，以便它们不再与每个代码一起上课。很抱歉，我不得不在这里写下很多代码，但我真的不知道其中哪些使它们相互冲突。

因此，如果 AU 列（第 47 列）中的“已发货”，我有此部分为任何空的销售和生产单元格返回“汇总”。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim counter As Long
Dim lastcolumn As Long

lastColumn = Me.Cells(1, Me.Columns.Count).End(xlToLeft).Column

'Shipped without Title Transfer
      If Me.Cells(1, Target.Column).Value = "MB51 Shipped" Then
        For counter = 1 To lastColumn
          If (Me.Cells(1, counter).Value = "Sales" Or Me.Cells(1, counter).Value = "Production") _
          And IsEmpty(Me.Cells(Target.Row, counter).Value) Then
            Me.Cells(Target.Row, counter).Value = "Rollup"
          End If
        Next counter
      End If

然后，如果最后一个销售列有“Title Transfer”，我将在 AX 列（第 50 列）中返回“x”。

Dim r As Range, r1 As Range, counter As Long
Dim MaxCol As Variant, rg As Range, j As Long

  If Not Intersect(Target, Range("N:AP")) Is Nothing And Target.Column Mod 4 = 2 Then
    Set r = Intersect(Target, Cells(1, 1).CurrentRegion, Columns(Target.Column).Resize(, 3))
    Call DoCells(r)
  End If

'Automatically put "x" if Title Transfer in any Sales columns
  If Not Intersect(Target, Range("N:AP")) Is Nothing And Target.Column Mod 4 = 2 Then
    If Target.CountLarge > 1 Then Exit Sub
    Set rg = Range("N" & Target.Row & ":AP" & Target.Row)
    MaxCol = 0
    For j = Columns("AP").Column To Columns("N").Column Step -4
      If Cells(Target.Row, j) <> "" Then
        If j > MaxCol Then MaxCol = j
      End If
    Next
    If MaxCol Mod 4 = 2 Then
      If Cells(Target.Row, MaxCol).Value = "Title Transfer" Then
        Cells(Target.Row, 50).Value = "x"
      Else
        Cells(Target.Row, 50).Value = ""
      End If
    End If
  End If

'This I have 8 Sales Column, however, I only put 1 line down for demonstration
Set r = Intersect(Target, Cells(1, 1).CurrentRegion, Columns(colSales1).Resize(, 3))
    If Not r Is Nothing Then Call DoCells(r)

If Not Intersect(Target, Range("N:AP")) Is Nothing And Target.Column Mod 4 = 2 Then
    If Target.CountLarge > 1 Then Exit Sub
    Set rg = Range("N" & Target.Row & ":AP" & Target.Row)
    MaxCol = Evaluate("=MAX(IF(" & rg.Address & "<>"""",COLUMN(" & rg.Address & ")))")
    If MaxCol Mod 4 = 2 Then
      If Cells(Target.Row, MaxCol).Value = "Title Transfer" Then
        Cells(Target.Row, 50).Value = "x"
      Else
        Cells(Target.Row, 50).Value = ""
      End If
    End If
  End If

End Sub

这是其中一行调用的 DoCells 子。

Private Sub DoCells(r As Range)
    Dim r1 As Range
    For Each r1 In r.Cells
        With r1
            Select Case .Column
                Case colSales1, colSales2, colSales3, colSales4, colSales5, colSales6, colSales7, colSales8
                    Call MasterChange(.Resize(1, 3))
                Case colProduction1, colProduction2, colProduction3, colProduction4, colProduction5, colProduction6, colProduction7, colProduction8
                    Call MasterChange(.Offset(0, -1).Resize(1, 3))
                Case colDay1, colDay2, colDay3, colDay4, colDay5, colDay6, colDay7, colDay8
                    Call MasterChange(.Offset(0, -2).Resize(1, 3))
            End Select
        End With
    Next
End Sub

这是我使用 Markdown Table 的数据结构：

| Title  | Engine Family | Market Segment | Customer | Engine Model | S/N | Build Spec | ACTL.FINISH | Sales Order | Item | Committed Date | EPS Date   | Target | Sales | Production | Day 1 | Status | Sales          | Production | Day 2          | Status | Sales   | Production | Day 3   | Status           | Sales   | Production | Day 4   | Status           | Sales   | Production | Day 5   | Status           | Sales   | Production | Day 6   | Status           | Sales   | Production | Day 7   | Status           | Sales   | Production | Day 8   | Status           | Comments | MB51 Shipped | FPS? | Plant | Title Transfer |
|--------|---------------|----------------|----------|--------------|-----|------------|-------------|-------------|------|----------------|------------|--------|-------|------------|-------|--------|----------------|------------|----------------|--------|---------|------------|---------|------------------|---------|------------|---------|------------------|---------|------------|---------|------------------|---------|------------|---------|------------------|---------|------------|---------|------------------|---------|------------|---------|------------------|----------|--------------|------|-------|----------------|
| Rollup | PS            | APU            | ABC      | 46C12        | 1   | BS1        | 0000-00-00  | 101         | 450  | 2019-12-31     | 2019-12-31 | Rollup | Green | Rollup     | Green | Sales  | Title Transfer | Yellow     | Title Transfer | Sales  |         |            |         | Sales/Production |         |            |         | Sales/Production |         |            |         | Sales/Production |         |            |         | Sales/Production |         |            |         | Sales/Production |         |            |         | Sales/Production |          |              |      |       | x              |
| Yellow | PS            | FEP            | ADG      | PS3          | 3   | BS3        | 0000-00-00  | 103         | 180  | 2019-12-16     | 2019-12-20 | Yellow | Green | Rollup     | Green | Sales  | Title Transfer | Yellow     | Title Transfer | Sales  | Rollup  | Rollup     | Rollup  | Sales/Production | Rollup  | Rollup     | Rollup  | Sales/Production | Rollup  | Rollup     | Rollup  | Sales/Production | Rollup  | Rollup     | Rollup  | Sales/Production | Rollup  | Rollup     | Rollup  | Sales/Production | Rollup  | Rollup     | Rollup  | Sales/Production |          | Shipped      |      |       |                |
| Rollup | T6T           | OEM            | FEDS     | 67C          | 5   | BS5        | 0000-00-00  | 105         | 250  | 2019-12-23     | 2019-12-22 | Rollup | Green | Rollup     | Green | Sales  | Title Transfer | Yellow     | Title Transfer | Sales  | Shipped | Rollup     | Shipped | Sales/Production | Shipped | Rollup     | Shipped | Sales/Production | Shipped | Rollup     | Shipped | Sales/Production | Shipped | Rollup     | Shipped | Sales/Production | Shipped | Rollup     | Shipped | Sales/Production | Shipped | Rollup     | Shipped | Sales/Production |          | Shipped      |      |       | x              |

仅供参考，我总共有 8 天，每一天都是 4 列的组合，顺序完全相同：销售、生产、天、状态。范围是从 N 列到 BS 列（或实际 Excel 工作簿中的 AS）。

从表中可以看出：

1）第一行完全符合我的要求。它确实正确评估列销售/列 R（第 2 天）中的“标题转移”是最后一个具有“标题转移”的销售列，并在 BX 列（或我的 excel 文件中的 AX 列）中返回一个“x” .

2）第二行，代码返回正确和错误的结果。我首先将“Title Transfer”放在销售列中，然后宏在 BX 列中返回一个“x”。这是正确的。

但是，当我在 BX 列中将“所有权转移”放在首位后，在 BU 列中输入“已发货”时，“x”被我在上面发布的已发货代码替换。当我将“已发货”放入 BU 列（我的 Excel 文件中的第 47 列或 AU 列）时，它确实为所有空的销售和生产单元格返回了“汇总”。但是表示所有权转移的“x”消失了。

所以这就是我过去一周一直在努力解决的问题。你能告诉我如何解决这个问题吗？3）如果BU列中的“Shipped”和BX列中的“x”（显然它不起作用），我希望我的代码执行第3行

简而言之，我的代码应该执行以下操作：

1) 如果 BU 列（实际 Excel 文件中的 AU）中为“已发货”且 BX 列中没有“x”（实际 Excel 文件中为 AX），则为所有空的销售和生产单元格返回“汇总”

2) 如果最后一个 Sales 列中的“Title Transfer”，而 BU 列（实际 Excel 文件中的 AU）中没有“Shipped”，则在 BX 列（实际 Excel 文件中的 AX）中返回“x”

3) 如果最后一个销售列中的“Title Transfer”（必须首先发生）和 BU 列中的“Shipped”（稍后发生）（实际 Excel 文件中的 AU），则在 BX 列中返回“x”，在列中返回“Shipped”所有空的销售和生产单元

你能帮助如何让它以这种方式工作吗？非常感谢，如果您需要更多信息，请告诉我。

Ps：这是MasterChange拥有的：

Public Sub MasterChange(SPD As Range)
    Dim rSales As Range
    Dim rProduction As Range
    Dim rDay As Range

    Set rSales = SPD.Cells(1, 1)
    Set rProduction = SPD.Cells(1, 2)
    Set rDay = SPD.Cells(1, 3)

    Application.EnableEvents = False
    If rSales = "Rollup" And rProduction = "Rollup" Then
        rDay = "Rollup"
    ElseIf rSales = "Rollup" And rProduction = "Green" Then
        rDay = "Green"
    ElseIf rSales = "Rollup" And rProduction = "Yellow" Then
        rDay = "Yellow"
'I have approximately 40 Ifs statements like those but above are just a few for demonstration
    End If
        Application.EnableEvents = True
    End Sub

标签： excelvba