CODEWITHSUNDEEP
vbaexcel
user7431724
user7431724

Reputation: 11

Define a range using an existing named range & offset/resize

I am trying to use an existing named range to reference different rows (the columns should be the same). Right now I have the following:

Dim DetailONE, DetailONW, DetailW, DetailQc As Range
Set DetailONE = Sheet11.Range("CM12:EG13")
Set DetailONW = Sheet11.Range("EI12:GC13")
Set DetailW = Sheet11.Range("GE12:HO13")
Set DetailQc = Sheet11.Range("HQ12:IT13")

And then I refer to those named ranges but I get an Application defined or object defined error with the following. Does anyone know why? I don't know if I need the .Address part afterwards either or if I can forego this.

Dim OntarioWestDet, OntarioEastDet, WestDet, QuebecDet As Range
Set OntarioWestDet = Sheet11.Range(DetailONW.Address).Offset(3, 0).Resize(300, 0)
Set OntarioEastDet = Sheet11.Range(DetailONE).Offset(3, 0).Resize(300, 0)
Set WestDet = Sheet11.Range(DetailWe).Offset(3, 0).Resize(300, 0)
Set QuebecDet = Sheet11.Range(DetailQc).Offset(3, 0).Resize(300, 0)

Upvotes: 0

Views: 1420

Answers (1)

SJR
SJR

Reputation: 23081

This works for me (there is a typo in one of your names

Sub x()

Dim DetailONE As Range, DetailONW As Range, DetailW As Range, DetailQc As Range
Set DetailONE = Sheet11.Range("CM12:EG13")
Set DetailONW = Sheet11.Range("EI12:GC13")
Set DetailW = Sheet11.Range("GE12:HO13")
Set DetailQc = Sheet11.Range("HQ12:IT13")

Dim OntarioWestDet As Range, OntarioEastDet As Range, WestDet As Range, QuebecDet As Range
Set OntarioWestDet = DetailONW.Offset(3, 0).Resize(300)
Set OntarioEastDet = DetailONE.Offset(3, 0).Resize(300)
Set WestDet = DetailW.Offset(3, 0).Resize(300)
Set QuebecDet = DetailQc.Offset(3, 0).Resize(300)

End Sub

Upvotes: 1

Related Questions