• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Naming a Non-Contiguous Range

W

willheap

Member
Hello All,


I have written a macro to (try to) create a non-contiguous named range in a worksheet containing 10,000+ lines of data.


Each of the 268 non-contiguous ranges is in column C and is 10 rows deep by 1 column wide starting at each cell containing "Process Components".


The main part of the code seems to work fine but when I try to add the non-contiguous range to a name I get an "Application-defined or object-defined error".


Can someone tell me why I get the "Application-defined or object-defined error" on the line that adds the name HRRange to the Newrange range? I have looked at this problem too long to see any errors now :(


Thanks for your help!

[pre]
Code: 
Sub HR_Range()

Application.ScreenUpdating = False

Dim UR As Long
Dim HRR As Range
Dim Newrange As Range

Set HRR = Sheets(6).Range(Cells(15, 3), Cells(46, 3))

Names.Add Name:="HRRange", RefersTo:=HRR

UR = Sheets(6).UsedRange.Rows.Count

For x = 46 To UR

For y = 0 To 9

If Cells(x - y, 3) = "Process Components" Then

Set Newrange = Application.Union(Newrange, Range(Cells(x, 3)))

End If

Next y

Next x

Names("HRRange").Delete

Names.Add Name:="HRRange", RefersTo:=Newrange

Application.ScreenUpdating = True

End Sub
[/pre]
 
Hi William ,


Can you try this ?

[pre]
Code: 
Sub HR_Range()

Application.ScreenUpdating = False

Dim UR As Long
Dim HRR As Range
Dim Newrange As Range

Sheets(6).Activate
Set HRR = Range(Cells(15, 3), Cells(46, 3))

Names.Add Name:="HRRange", RefersTo:=HRR

Set Newrange = Nothing

For x = 46 To UR
For y = 0 To 9
If Cells(x - y, 3) = "Process Components" Then
If Newrange Is Nothing Then
Set Newrange = Range(Cells(x, 3).Address)
Else
Set Newrange = Application.Union(Newrange, Range(Cells(x, 3).Address))
End If
End If
Next y
Next x

Names("HRRange").Delete
Names.Add Name:="HRRange", RefersTo:=Newrange

Application.ScreenUpdating = True

End Sub
[/pre]
Narayan
 
Hello Narayan,


Again, you have come to my rescue! I must owe you several beers by now!


However, running your code still gives the same error in the same place. Any other ideas?


I am running this code from a module but I can't see why that would cause the problem.


Just a query; how does the loop proceed with no UR value set in your code "For x = 46 to UR"??


Thanks again Narayan!
 
Hi William ,


I tried out the code , using a numeric value for UR e.g. UR = 50.


Everything worked , right down to the creation of HRRange the seoond time !


Let me try once more and I'll get back to you.


Narayan
 
Hi William ,


I just tried out this :

[pre]
Code: 
Sub HR_Range()

Application.ScreenUpdating = False

Dim UR As Long
Dim HRR As Range
Dim Newrange As Range

Sheets(6).Activate
Set HRR = Range(Cells(15, 3), Cells(46, 3))

Names.Add Name:="HRRange", RefersTo:=HRR

Set Newrange = Nothing
UR = 50
For x = 46 To UR
For y = 0 To 9
'If Cells(x - y, 3) = "Process Components" Then
If Newrange Is Nothing Then
Set Newrange = Range(Cells(x, 3).Address)
Else
Set Newrange = Application.Union(Newrange, Range(Cells(x, 3).Address))
End If
'End If
Next y
Next x

Names("HRRange").Delete
Names.Add Name:="HRRange", RefersTo:=Newrange

Application.ScreenUpdating = True

End Sub
[/pre]
Everything works , and the following definition for HRRange is created : =Sheet1!$C$46:$C$50


You can see that UR has been initialized to 50 ; also the IF statement has been commented.


Without excluding the IF statement , I get an error ; it probably means Newrange is never getting a proper physical address , so that when Excel tries to add the name HRRange , it fails.


Can you check whether the conditions of the IF statement will ever be fulfilled ?


What you can do is place your cursor on the following statement :


Names.Add Name:="HRRange", RefersTo:=Newrange


and press the F9 key , so as to set a Breakpoint at this statement. Now when you run the procedure , execution will pause when this statement is encountered.


Now , in the Immediate Window , type in :


?Newrange.Address


and see what is displayed.


You can also type in :


?Newrange is Nothing


and see whether TRUE is displayed or FALSE is displayed.


Happy debugging !


Narayan
 
Hi

As sayed by NARAYANK991, Maybe you should check again about NewRange


Code: 
If Not Newrange Is Nothing Then Names.Add Name:="HRRange", RefersTo:=Newrange
 
hi,


whilst looking for something also relating to non contiguos named ranges, i came across this post and thought i would add my 2 pennies worth


why use a defined range when you can build the range as a string


'Sub HR_Range()


Application.ScreenUpdating = False


Dim UR As Long

Dim HRR As Range

'Dim Newrange As Range - commented out

'Dim Newrange as string

Sheets(6).Activate

Set HRR = Range(Cells(15, 3), Cells(46, 3))


Names.Add Name:="HRRange", RefersTo:=HRR


' Set Newrange = Nothing commented out

Newrange=""

UR=50

For x = 46 To UR

' For y = 0 To 9 loop not needed

If Cells(x, 3) = "Process Components" Then

If Newrange ="" Then

Newrange = "=" & Range(Cells(x+1, 3),cells(x+9,3).Address

Else

Newrange = Newrange & "," & Range(Cells(x+1, 3),cells(x+9,3)).Address

End If

End If

Next x


Names("HRRange").Delete

Names.Add Name:="HRRange", RefersTo:=Newrange


Application.ScreenUpdating = True


End Sub'
 
Thank you all!! Your response is very much appreciated.


I will check your code out today and let you know how it goes.


(It looks like I need to get to grips with the requirement for .Address)
 
You must log in or register to reply here.
Back
Top