VBA数组从数据库打印数据时忽略指定列的实现问题

1. 纠正逻辑误解 #

你提到的myRng定义代码没有问题，它只是选中了「Quote Database」表中B列从B3到最后一行有数据的单元格。问题出在数据复制的循环逻辑：原代码按顺序取数据库行中从B列开始的每一列，没有跳过不需要的单元格。

2. 两种修改方式 #

方式一：跳过指定列（快速修改）

如果你知道哪些数据库列需要忽略（相对于myCell的偏移量，比如偏移量4对应F列），可以在循环中添加判断，跳过这些列：

Sub AlterQuote()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddr As Variant
Dim lOrders As Long

Application.ScreenUpdating = False

Set FormWks = Sheets("Quote")
Set DataWks = Sheets("Quote Database")

' 保留原目标单元格数组
myAddr = Array("G9", "G10", "G11", "G12", "C14", "C15", "C16", "C17", "C18", "C19", "C20", "C21", "B25", "C25", "D25", "E25", "F25", "G25", "H25", "I25", "H38", "I38", "B26", "C26", "D26", "E26", "F26", "G26", "H26", "I26", "H39", "I39", "B27", "C27", "D27", "E27", "F27", "G27", "H27", "I27", "H40", "I40", "B28", "C28", "D28", "E28", "F28", "G28", "H28", "I28", "H41", "I41", "B29", "C29", "D29", "E29", "F29", "G29", "H29", "I29", "H42", "I42", "I30", "H31", "H32", "H33", "H43", "I43", "H44", "H45", "H46", "D57", "D58", "D59", "D60")

With DataWks
  Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
  With myCell
    ' 修改判断：只有当A列（偏移-1）是"x"时才执行复制
    If .Offset(0, -1).Value = "x" Then
      .Offset(0, -1).ClearContents
    
      For iCtr = LBound(myAddr) To UBound(myAddr)
          ' 跳过不需要的偏移列，比如这里跳过偏移量为3和5的列（对应E列、G列）
          ' 你可以根据自己的需求修改这个判断条件
          If iCtr <> 3 And iCtr <> 5 Then
              FormWks.Range(myAddr(iCtr)).Value = myCell.Offset(0, iCtr).Value
          End If
      Next iCtr
    End If
  End With
Next myCell

MsgBox "quote can now be altered on Quote Sheet"
Application.ScreenUpdating = True

End Sub

方式二：精准映射（更清晰可控）

如果需要更灵活地控制哪些数据库列对应哪些目标单元格，可以定义两个数组：一个存储目标单元格地址，另一个存储对应的数据库列偏移量，只保留需要的映射关系：

Sub AlterQuote()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddr As Variant
Dim colOffsets As Variant ' 新增：存储对应数据库列的偏移量
Dim lOrders As Long

Application.ScreenUpdating = False

Set FormWks = Sheets("Quote")
Set DataWks = Sheets("Quote Database")

' 只保留需要的目标单元格，以及对应的数据库列偏移量（相对于myCell，即B列）
' 示例：G9对应B列（偏移0），G10对应C列（偏移1），跳过G11（原偏移2），G12对应D列（偏移3）
myAddr = Array("G9", "G10", "G12", "C14", "C15") ' 按需删减目标单元格
colOffsets = Array(0, 1, 3, 4, 5) ' 对应每个目标单元格的数据库列偏移

With DataWks
  Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
  With myCell
    If .Offset(0, -1).Value = "x" Then
      .Offset(0, -1).ClearContents
    
      For iCtr = LBound(myAddr) To UBound(myAddr)
          FormWks.Range(myAddr(iCtr)).Value = myCell.Offset(0, colOffsets(iCtr)).Value
      Next iCtr
    End If
  End With
Next myCell

MsgBox "quote can now be altered on Quote Sheet"
Application.ScreenUpdating = True

End Sub

3. 关键说明 #

• 原代码的判断逻辑是「A列不为空则执行」，我修改为「A列等于"x"时执行」，符合你描述的「单元格标记特定值（x）时触发」的需求，如果你不需要可以改回原判断。
• 偏移量说明：myCell.Offset(0, n) 表示myCell所在单元格往右n列，比如n=0是B列，n=1是C列，以此类推。你可以根据自己要忽略的列调整偏移量参数。

内容的提问来源于stack exchange，提问作者Charlotte