Declare a range variable as another range variable, resized

[gcalder]

Hi, I'm writing my first macro, and something I'm trying to do as part of it is refer to a range that is a resized version of another range I have declared. My current code returns a Run-time error 1004, Method Range of object _global failed. An example of how I've tried to code it is below:

Dim rng1 as Range
Set rng1 = Selection

Dim rng2 as Range
Set rng2 = Range(rng1).Resize(0,-1)

The macro automates actions across two sheets, so my thinking is that maybe I need to declare the workbook and sheet of both ranges, however I can't find the syntax for that when declaring the second range

 

[Peter_SSs]

Welcome to the MrExcel board!

Your problem is that you are trying to say that rng2 should be 0 rows high and -1 columns wide which is impossible.

Perhaps you mean offset which would set rang2 to be immediately to the left of rng1

Set rng2 = Range(rng1).Offset(0,-1)

Otherwise you would need to explain in words just what you are trying to do.

 

[gcalder]

Welcome to the MrExcel board!

Your problem is that you are trying to say that rng2 should be 0 rows high and -1 columns wide which is impossible.

Perhaps you mean offset which would set rang2 to be immediately to the left of rng1

Set rng2 = Range(rng1).Offset(0,-1)

Otherwise you would need to explain in words just what you are trying to do.

Hi Peter,

Thanks for your help, I'll try using offset to achieve the result I'm going for. Just in case though, I'll try explaining what I'm trying to do better.

The macro is designed to be executed once I have selected a cell in the workbook, e.g. C5, this could be any cell though. When the macro is executed, this position is declared as rng1, a few lines down the code, I create rng2 which I am trying to set to include the cell that is in rng1, as well as the cell directly to the left of it. Following from the previous example, I'd want rng2 to be B5:C5.

More succinctly, I don't just want to move the range one cell to the left, I want it to expand to include the range of rng1 AND the cell to the left

 

[Alex Blakenburg]

Just modify Peter's code slightly.

Set rng2 = Range(rng1).Offset(0,-1).resize(1,2)

 

[gcalder]

Thanks Alex,

That's what I thought to try as well after reading Peter's code, but unfortunately I'm still receiving the same error which leads me to believe my mistake must lie elsewhere. The code isn't very big as its my first macro, so I'll post it below if that is useful. Warning in advance its probably not filled with best practice code hahaha.

Sub SheetBuilderV2()

' SheetBuilderV2 Macro


    Dim WB As Workbook
    Set WB = ThisWorkbook
    Dim InitialRange As Range   ' creates a range and sets it to be the selected cell
    Set InitialRange = Selection
    Dim NSName As String    ' creates a string and sets it to be the selected cell, note that Set isn't required as this is a string data type, not an object
    NSName = Selection.Value
    Dim NS As Worksheet         ' creates a worksheet variable, "NS"
    Sheets.Add.Name = NSName
    ActiveSheet.Move After:=Sheets(Sheets.Count) ' adds a new worksheet, after the last worksheet
    Set NS = ActiveSheet        ' sets the worksheet NS to be the activesheet, which should be the newly created worksheet
    NS.Select

    
    Sheets("Master List").Select    ' the next 28 lines are pretty much just the copying and pasting of the sheet formatting into the new sheet
    Range("A1:B1").Select
    Application.CutCopyMode = False
    Selection.Copy
    NS.Select
    ActiveSheet.Paste
    Sheets("Master List").Select
    Range("J3:J4").Select
    Application.CutCopyMode = False
    Selection.Copy
    NS.Select
    Range("A4").Select
    ActiveSheet.Paste
    Sheets("Master List").Select
    Range("J7:M7").Select
    Application.CutCopyMode = False
    Selection.Copy
    NS.Select
    Range("A9").Select
    ActiveSheet.Paste
    
    Sheets("Master List").Select
    Dim CountryAndOutletRange As Range  ' creates a range which contains the original outlet resized to also select the cell to the left of it
    Set CountryAndOutletRange = Range(InitialRange).Offset(0, -1).Resize(1, 2) ' [COLOR=rgb(184, 49, 47)]up to here is exectued successfully, this line does not run[/COLOR]
    Range(CountryAndOutletRange).Activate
    Application.CutCopyMode = False
    Selection.Copy
    NS.Select
    Range("A2").Select
    ActiveSheet.Paste
    
    
    Range("B4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=CONCAT('Master List'!R[-1]C[9],'Master List'!R[-2]C[1],'Master List'!R[-1]C[11])"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCAT('Master List'!R[-1]C[9],'Master List'!R[-3]C[1],'Master List'!R[-1]C[11],'Master List'!R[-3]C[2],'Master List'!R[-1]C[12])"
    Range("B6").Select
    Sheets("Master List").Select
    Range("B3").Select
End Sub

 

[Peter_SSs]

You, Alex & I have all made an error with our syntax. It should be

Set rng2 = rng1.Offset(0, -1).Resize(1, 2)

 

[gcalder]

I've corrected the syntax per your code Peter, but unfortunately I'm still receiving the same error message.

 

[Peter_SSs]

I've corrected the syntax

I assume that you did that in two locations.
I believe that your problem is that you are trying to activate a range that is not on the active sheet when the code gets to that line. That of course is not possible.
The problem line would be the last one here (I have shown the two corrections re the above conversation.)

Set CountryAndOutletRange = Range(InitialRange).Offset(0, -1).Resize(1, 2)
Set CountryAndOutletRange = InitialRange.Offset(0, -1).Resize(1, 2)
Range(CountryAndOutletRange).Activate
CountryAndOutletRange.Activate

In general you do not have to activate worksheets or select ranges to work with them and doing so slows your code.

Difficult to suggest a complete alternative code though as I am unfamiliar with exactly what you are trying to do.
BTW, If you want to format/highlight any code in the forum, as you tried with the first line above, you need to use the 'RICH' code tags:

 

[gcalder]

Peter, you bloody legend!!! Got it going now. I have no doubt there is a more efficient way to write the macro I'm trying to make, its all part of the learning process though I suppose. Thanks so much for your help, and Alex too. Couldn't learn this without people like you.

 

[Peter_SSs]

Glad we were able to help. Thanks for the follow-up.