Storing a VBA Range in a variable

Question

I'm trying to define a dynamic range in VBA for some operations I have to do with a matrix, as follows:

Dim Range1 As Range, Range2 As Range
'...
'define some indexes
'...
Set Range1 = Sheets("Sheet1").Range(Cells(rowFirst, colFirst), Cells(rowEnd,colEnd))

But I'm getting the runtime error #1004, and I don't know why and how to fix it.

I'm new to VBA so I was expecting you could help me.

Thanks to all!

Note: The rowFirst,colFirst,rowEnd,colEnd variables are constantly changing since I need to be moving in the worksheet from matrix to matrix.

Edit

Thank you all for your responses, but I'm still getting the same error on the same line. Let me explain a little bit deeper what's what I'm trying to do. I need to take a 13x91(in cells) matrix and transpose it, then I need to join all in one single column, and put the resultant column in the Sheet 2. This done 20 times advancing in columns (and not in rows). Here I leave the code:

    Sub ConvertRangeToColumn()
        Dim it As Integer
        Dim rowInit As Integer, rowEnd As Integer, colInit As Integer, colEnd As Integer
        rowInit = 1
        rowEnd = 91
        colInit = 1
        colInit = 13
        For it = 1 To 20
            Dim Range1 As Range, Range2 As Range, Rng As Range
            Dim rowIndex As Integer
            xTitleId = "Automatización"
            Set Range1 = Hoja1.Range(Hoja1.Cells(rowInit, colInit), Hoja1.Cells(rowEnd, colEnd))
            Set Range2 = Sheets("Hoja2").Range(Cells(1, it))
            colInit = colInit + 13
            colEnd = colEnd + 13
            rowIndex = 0
            Application.ScreenUpdating = False
            For Each Rng In Range1.Rows
                Rng.Copy
                Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
                rowIndex = rowIndex + Rng.Columns.Count
            Next
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
        Next it
    End Sub

Note 2: Since I work in Excel in Spanish, the Hoja1 object means Sheet1

Sheets("Sheet1").Range(Sheets("Sheet1").Cells(rowFirst, colFirst), Sheets("Sheet1").Cells(rowEnd,colEnd)) Assign Parentage to ALL range objects. — Scott Craner
– Scott Craner, Commented Aug 23, 2016 at 16:53

Scott Craner · Accepted Answer · 2016-08-23 18:29:47Z

3

As per your new code three things:

Hoja1 should be Worksheets("Hoja1")
You never assigned value to colEnd but had colInit twice.
you cannot put one Cells() inside a Range(). Just use the Cells() by itself.

code:

Sub ConvertRangeToColumn()
    Dim it As Integer
    Dim rowInit As Integer, rowEnd As Integer, colInit As Integer, colEnd As Integer
    rowInit = 1
    rowEnd = 91
    colInit = 1
    colEnd = 13
    For it = 1 To 20
        Dim Range1 As Range, Range2 As Range, Rng As Range
        Dim rowIndex As Integer
        xTitleId = "Automatización"
        Set Range1 = Worksheets("Hoja1").Range(Worksheets("Hoja1").Cells(rowInit, colInit), Worksheets("Hoja1").Cells(rowEnd, colEnd))
        Set Range2 = Worksheets("Hoja2").Cells(1, it)
        colInit = colInit + 13
        colEnd = colEnd + 13
        rowIndex = 0
        Application.ScreenUpdating = False
        For Each Rng In Range1.Rows
            Rng.Copy
            Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
            rowIndex = rowIndex + Rng.Columns.Count
        Next
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    Next it
End Sub

answered Aug 23, 2016 at 18:29

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mathieu Guindon Over a year ago

Arguably row numbers should be Long integers, and there's still too much Application.ScreenUpdating toggling goign on (turning off should be before the outer loop, turning back on should be after Next it and before End Sub), but I don't see where this code could fail. Have an upvote!

Scott Craner Over a year ago

@Mat'sMug arguably this code could go through a major rewrite to speed things up, but I will leave that to the OP to post on codereview for you wizards to tear apart.

sant016 Over a year ago

Thanks a lot @ScottCraner, you're the best!

Mathieu Guindon Over a year ago

@sant016 IKR? He should totally be among the wizards that review and help improve working VBA code over at Code Review! did I write that out loud?

Techie · Accepted Answer · 2016-08-23 16:58:41Z

1

Try below code..cells should be referenced with the sheet.

       Dim Range1 As Range, Range2 As Range
       '...
      'define some indexes
         Set Range1 = Sheets("Sheet1").Range(Sheets("Sheet1").Cells(rowFirst, colFirst), Sheets("Sheet1").Cells(rowEnd,colEnd))

answered Aug 23, 2016 at 16:58

Techie

1814 silver badges14 bronze badges

1 Comment

sant016 Over a year ago

Although in the edit it's written different, I tried this option also and didn't work.

Mathieu Guindon · Accepted Answer · 2016-08-23 18:29:16Z

1

The Sheets function is implicitly referring to the Sheets collection of the ActiveWorkbook. You probably mean to use the Worksheets collection, which won't contain "chart sheets" and other non-worksheet objects.

Unqualified, Range implicitly refers to the ActiveSheet; same for an unqualified Cells call.

Range(cell1, cell2) requires that cell1 and cell2 are on the same sheet as whichever sheet object is qualifying the Range call - if Range is unqualified, then working off the active sheet will work.

But here Range is qualified with Sheets("Sheet1"), so cell1 and cell2 must also be on that same Sheets("Sheet1") worksheet, otherwise you get the error you're getting.

Also, if Sheets("Sheet1") is in ThisWorkbook (the workbook with the running VBA code), then you can refer to it by its global object (which you can rename in the properties toolwindow), using its code name - by default that would be Sheet1 for, well, the "Sheet1" worksheet.

In other words:

Set Range1 = Sheet1.Range(Sheet1.Cells(rowFirst, colFirst), Sheet1.Cells(rowEnd,colEnd))
Set Range2 = Sheet2.Range(Sheet2.Cells(1, it))

edited Aug 23, 2016 at 18:29

answered Aug 23, 2016 at 17:11

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

2 Comments

sant016 Over a year ago

Tried this too and didn't work, check my edit please. And thanks.

Mathieu Guindon Over a year ago

You have the exact same problem on the Set Range2 assignment too. I think Scott sorted it out now.

bdpolinsky · Accepted Answer · 2016-08-23 18:48:12Z

0

Error 1004 = This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.

-> Does one of the values in your cells have more than 911 characters?

answered Aug 23, 2016 at 18:48

bdpolinsky

4797 silver badges21 bronze badges

Collectives™ on Stack Overflow

Storing a VBA Range in a variable

Edit

4 Answers 4

4 Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit

4 Answers 4

4 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related