在范围定义中使用列的变量

Question

我想在数组中使用范围的内容。它可以工作，直到我在范围定义中添加了单元格(R，C)-method的列的变量。所以我的问题在范围(单元格(标题，ic_from_col)，单元格(标题，ic_to_col))是确定的

运行时错误'1004‘应用程序或面向对象错误“<

我遗漏了什么？我能试试什么？

    Dim export As String: export = "Table1"
    Dim headline As Integer: headline = 7
    Dim ic_from_col As Integer: ic_from_col = 30
    Dim ic_to_col As Integer: ic_to_col = 40
    Dim ICNames As Variant: Set ICNames = Sheets(export).Range(Cells(headline, ic_from_col), Cells(headline, ic_to_col))
    Dim key As String: key = 1
    For Each item In SomethingElse
      ...
      name = ICNames(key)
    Next item

(我通过搜索得到列和行的数字。这是可行的，所以我只在这里放了一些数字。解释我为什么要使用变量)。

我尝试了以下建议，但我得到了同样的结果：

Dim ICNames As Variant
ICNames = Sheets(export).Range(Cells(headline, ic_from_col), Cells(headline, ic_to_col))

让我展示一下如何解释我的问题：

  Dim export As String: export = "Table"
    Dim headline As Long: headline = 7
    Dim ic_from_col As Long: ic_from_col = 32
    Dim ic_to_col As Long: ic_to_col = 42
    Dim ICNames As Variant: Set ICNames = Sheets(export).Range("AF" & headline & ": AQ" & headline)
'    Dim ICNames As Variant: Set ICNames = Sheets(export).Range(Cells(headline, ic_from_col), Cells(headline, ic_to_col))
    Dim key As String: key = 1
      Name = ICNames(key)
      MsgBox (Name)

Answer 1

所以，我就是这样解决我的案子的。我用过".address"：

ICNames = Sheets(export).Range(Cells(headline, ic_from_col).Address & ":" & Cells(headline, ic_to_col).Address)

Answer 2

如果工作表名称是正确的，那么您需要删除设置键，因为它不是对象。

ICNames = Sheets(export).Range(Cells(headline, ic_from_col), Cells(headline, ic_to_col))