Use variables for columns in Range definition-CodePudding

I want to use the content of a range in an array. It works until I put in variables for the columns with Cells(R,C)-method in the range definition. So my problem is definitive in Range(Cells(headline, ic_from_col), Cells(headline, ic_to_col))

Run time error '1004' Application or object-oriented error" <

What am I missing? What could I try?

    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

(I get the numbers for columns and row by searches. Which works, so I put just some numbers in here for that. Just to explain why I want to use variables).

I tried the following as recommended, but I've got the same result:

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

Let me show what works to explain my problem:

  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)

CodePudding user response：

So, this is how I solved it for my case. I have used ".address":

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

CodePudding user response：

If sheet name is correct then you need to remove Set keywork, since it is not object.

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