Excel VBA需求：酒店变更时插入列并添加最小值公式

需求说明 #

要实现运营商间酒店价格对比，达成以下效果：

• 每当酒店名称变更时，在该酒店最后一个运营商列右侧插入新列
• 新增列中自动添加公式，计算对应酒店所有运营商价格的最小值

现有代码问题 #

原代码仅针对运营商"Z"的列执行插入操作，一旦某酒店没有运营商Z，就无法触发插入列的逻辑，完全满足不了需求。原代码如下：

Sub Insert()
'
Dim x As Long, rng As Range

Set rng = Range("B2:DD2")

For Each cell In rng
    If cell = "Z" Then
        cell.EntireColumn.Offset(0, 1).Insert (xlShiftToRight)
    End If
Next cell

End Sub

修复后的完整代码 #

下面的代码从右往左遍历列，通过检测酒店名称的变化触发列插入，同时自动填充最小值公式，兼容所有酒店（包括无运营商Z的情况）：

Sub InsertMinColumnPerHotel()
    Dim lastCol As Long
    Dim i As Long
    Dim priceStartRow As Integer ' 价格数据起始行，可根据实际调整
    
    ' 初始化参数，根据你的表格实际结构调整
    priceStartRow = 3
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column ' 获取表头最后一列
    
    ' 从右往左遍历，避免插入列打乱后续列的索引
    For i = lastCol To 2 Step -1
        ' 判断当前列是否为对应酒店的最后一列
        If i < lastCol Then
            If Cells(1, i).Value <> Cells(1, i + 1).Value Then
                Call AddMinColumn(i)
            End If
        Else
            ' 处理最后一列的酒店
            Call AddMinColumn(i)
        End If
    Next i
End Sub

' 封装插入列并添加MIN公式的逻辑
Sub AddMinColumn(targetCol As Integer)
    Dim firstColOfHotel As Integer
    Dim lastRow As Long
    
    ' 查找当前酒店的第一列位置
    firstColOfHotel = targetCol
    Do While firstColOfHotel > 1 And Cells(1, firstColOfHotel - 1).Value = Cells(1, targetCol).Value
        firstColOfHotel = firstColOfHotel - 1
    Loop
    
    ' 在目标列右侧插入新列
    Columns(targetCol + 1).Insert Shift:=xlShiftToRight
    
    ' 设置新增列表头
    Cells(1, targetCol + 1).Value = Cells(1, targetCol).Value & " 最低价"
    Cells(2, targetCol + 1).Value = "MIN"
    
    ' 获取当前酒店价格的最后一行
    lastRow = Cells(Rows.Count, firstColOfHotel).End(xlUp).Row
    
    ' 写入MIN公式并填充至所有行
    Cells(priceStartRow, targetCol + 1).Formula = "=MIN(" & Cells(priceStartRow, firstColOfHotel).Address & ":" & Cells(priceStartRow, targetCol).Address & ")"
    Cells(priceStartRow, targetCol + 1).AutoFill Destination:=Range(Cells(priceStartRow, targetCol + 1), Cells(lastRow, targetCol + 1))
End Sub

代码关键点说明 #

• 从右往左遍历：插入列会改变后续列的索引，从右往左遍历可避免索引偏移导致的漏处理问题
• 酒店分组自动检测：通过循环定位同一酒店的所有列范围，不依赖特定运营商标识，适配所有酒店场景
• 模块化逻辑：把插入列和公式填充封装为独立子过程，代码更易维护
• 灵活配置：priceStartRow参数可根据你的表格实际数据起始行调整

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