如何在Excel VBA中设置跨列而非跨行的单元格范围？

我正在创建一个用于大型宏的公式，希望将EOMONTH公式从第一个日期所在单元格开始，沿行拖动到最后一个有内容的列。当前使用的VBA代码如下：

Sub DragFormula()
    'Declare variables
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim formulaRange As Range

    'Set the worksheet to use
    Set ws = ThisWorkbook.Sheets("Sheet1")

    'Find the last filled cell in row 1
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    'Set range to apply the formula to
    Set formulaRange = ws.Range("B1:B" & lastCol)

    'Enter the formula
    formulaRange.Formula = "=EOMONTH(A1,1)"
End Sub

但公式被沿B列向下复制而非横向复制。修改范围为Set formulaRange = ws.Range("B1:" & lastCol)时，出现错误：

Run-time error '1004': Method 'Range' of object '_Worksheet' failed

请问如何设置跨列而非跨行的范围？

问题核心是范围定义的格式错误：

• 原代码"B1:B" & lastCol生成的是B1:B5这类纵向区域（B列第1到第5行），所以公式会向下复制。
• 直接拼接"B1:" & lastCol无效，因为Range需要的是合法单元格地址（如B1:F1），而lastCol是数字格式的列号，无法直接组合成有效地址。

提供两种可靠的修正方法：

方法1：用Cells对象直接定义横向范围 #

Cells语法为Cells(行号, 列号)，可以直接指定横向区域的首尾单元格，无需转换列号：

Sub DragFormula()
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim formulaRange As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    ' 定义第1行从B列（第2列）到lastCol列的横向范围
    Set formulaRange = ws.Range(ws.Cells(1, 2), ws.Cells(1, lastCol))

    ' 公式使用相对引用，确保横向拖动时自动匹配对应列的单元格
    formulaRange.Formula = "=EOMONTH(A1,1)"
End Sub

方法2：将列号转换为列字母后拼接地址 #

如果习惯使用A1样式地址，可以通过单元格地址提取列字母：

Sub DragFormula()
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim lastColLetter As String
    Dim formulaRange As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' 从单元格地址中提取列字母
    lastColLetter = Split(ws.Cells(1, lastCol).Address, "$")(1)

    ' 拼接成B1:X1这类横向范围地址
    Set formulaRange = ws.Range("B1:" & lastColLetter & "1")

    formulaRange.Formula = "=EOMONTH(A1,1)"
End Sub

两种方法都能正确定义横向范围，推荐第一种，无需额外的列号转换逻辑，更稳定不易出错。

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