CODEWITHSUNDEEP
vbaexcel
Looker
Looker

Reputation: 144

Run-time error 1004 while setting ranges

I'm trying to make an array of ranges, but I'm getting "application-defined or object-defined error". The error is appearing on the line with Set Rng. The format should be fine, even if the line is somewhat long. I've specified the sheet I'm trying to get the ranges from, and the sub is currently in Module 1. It could just be a typo error somewhere, but after having re-checked the line six times I'd be disappointed if that was it.

Sub TableRange(ByVal Target As Range)
   Dim Rng As Range
   Dim Area As Range
   Set Rng = Worksheets("Tables").Range("A3:D23,A28:C39,A44:E61,A66:C102,A107:E121,A126:C135,A140:C149,A153:C162,A167:C192,A197:F215,A220:C269,A274:D282,A287:D295,A300:D304")
   Set Rng = Union(Rng, Worksheets("Tables").Range("A309:C358,A363:C389,A394:C412,A417:C437,A442:C462,A467:D475,A480:D487,A492:C531,A536:D544,A549:D557,A562:C574,A579:D598,A603:D622"))

   For Each Area In Rng.Areas
      If Not Intersect(Target, Worksheets("Tables").Range(Area)) Is Nothing Then
         'do stuff
      End If
   Next Area
End Sub

If there is anything else I should mention or that I can do to improve my question, let me know and I'll edit my post accordingly.

Update: Range array has been fixed, thanks to @user3964075, but now I seem to be stuck with the same error on If Not Intersect - Is Nothing Then

Upvotes: 1

Views: 721

Answers (3)

user4039065
user4039065

Reputation:

You are not dealing with the Range.Areas property properly. Try a loop through the index.

dim a as long
For a =1 to Rng.Areas.count
   If Not Intersect(Target, Rng.Areas(a)) Is Nothing Then
      'do stuff
   End If
Next a

Upvotes: 1

BrakNicku
BrakNicku

Reputation: 5990

The code is fine, but string constant exceeds maximum length (255 chars) allowed for Range property. You can easily fix it with:

Set Rng = Worksheets("Tables").Range("A3:D23,A28:C39,A44:E61,A66:C102,A107:E121,A126:C135,A140:C149,A153:C162,A167:C192,A197:F215,A220:C269,A274:D282,A287:D295,A300:D304")
Set Rng = Union(Rng, Worksheets("Tables").Range("A309:C358,A363:C389,A394:C412,A417:C437,A442:C462,A467:D475,A480:D487,A492:C531,A536:D544,A549:D557,A562:C574,A579:D598,A603:D622"))

Maybe consider using Named Range instead of this long list.

EDIT: To fix the second problem, change it to:

If Not Intersect(Target, Area) Is Nothing Then
     'do stuff
End If

Upvotes: 3

Felix
Felix

Reputation: 115

I copied the Set Rng Line and had the same error. When I removed one range, i.e. A28:C39 it works. It does not matter which Range will be removed. Maybe there is a maximum of Ranges?

Try:

Set Rng = Worksheets("Tabelle1").Range("A3:D23,A44:E61,A66:C102,A107:E121,A126:C135,A140:C149,A153:C162,A167:C192,A197:F215,A220:C269,A274:D282,A287:D295,A300:D304,A309:C358,A363:C389,A394:C412,A417:C437,A442:C462,A467:D475,A480:D487,A492:C531,A536:D544,A549:D557,A562:C574,A579:D598,A603:D622")

Upvotes: 0

Related Questions