如何将Excel中为单个单元格编写的VBA宏应用到其他单元格范围

核心逻辑是通过循环遍历指定行范围，将原代码中固定的行号替换为动态循环变量即可实现批量生效。

完整修改后代码 #

Sub 批量计算H列()
    Dim standard As Variant
    Dim i As Long
    ' 下方两个数值修改为你实际需要处理的起止行号
    Const startRow As Long = 2
    Const endRow As Long = 200
    
    For i = startRow To endRow
        standard = Range("G" & i) ' 读取当前行G列的标准参数
        
        If standard = "medium" Or standard = "heavy" Then
            Range("H" & i).Formula = "=-85*PI()*(D" & i & "/1000)^2+ 724.88*(D" & i & "/1000)"
        ElseIf standard = "600/3" Then
            Range("H" & i).Formula = "=-85*PI()*(D" & i & "/1000)^2+ 724.88*(D" & i & "/1000)+0.98*(2*PI()*D" & i & "*C" & i & " + 2*PI()*D" & i & "^2)"
        End If
    Next i
End Sub

调整说明 #

• 你只需要修改代码中startRow和endRow的常量值，即可指定批量处理的行范围
• 如果你需要只处理你当前选中的单元格所在行，可以使用下面的适配版本：

Sub 选中区域批量计算H列()
    Dim standard As Variant
    Dim cell As Range
    
    For Each cell In Selection
        i = cell.Row ' 取选中单元格所在行号
        standard = Range("G" & i)
        
        If standard = "medium" Or standard = "heavy" Then
            Range("H" & i).Formula = "=-85*PI()*(D" & i & "/1000)^2+ 724.88*(D" & i & "/1000)"
        ElseIf standard = "600/3" Then
            Range("H" & i).Formula = "=-85*PI()*(D" & i & "/1000)^2+ 724.88*(D" & i & "/1000)+0.98*(2*PI()*D" & i & "*C" & i & " + 2*PI()*D" & i & "^2)"
        End If
    Next cell
End Sub

使用时先选中你要处理的所有行对应的任意单元格，再运行宏即可只处理选中行。

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