hobbiton73
07-29-2013, 02:34 AM
Hi, I wonder whether someone may be able to help me please.
I'm using the code below, which, in addition to creating a specific worksheet and copying and pasting a column header range, creates a named range called "Staff Name".
Option Explicit
Sub NamedRange()
Dim rMyRg As Range
Set rMyRg = Range([A1], [A1].End(xlDown))
ActiveWorkbook.Names.Add Name:="StaffName", RefersToR1C1:="=" & _
ActiveSheet.Name & "!" & rMyRg.Address(ReferenceStyle:=xlR1C1)
Worksheets.Add(After:=Worksheets(1)).Name = "Calculations"
Sheets("HMRC Project Data Download1").Select
Range("A1:H1").Select
Selection.Copy
Sheets("Calculations").Select
Range("B2").Select
ActiveSheet.Paste
End Sub
The codes works fine and the named range is successfully created
What I'm now trying to do is create a unique list from this named range using using VBA.
I've found a number of posts and tutorials which explains how to do this, one of which I've added below:
Sub listUnique()
Application.ScreenUpdating = False
Dim c As Range
Dim counter As Long
counter = 1
For Each c In Range("StaffName")
If WorksheetFunction.CountIf(Range("T:T"), c.Value) = 0 Then
Cells(counter, "T").Value = c.Value
counter = counter + 1
End If
Next c
Application.ScreenUpdating = True
End Sub
The problem I'm having with this, and all the other code examples I've used, is that when I run the code I receive the following error:
Run time error '1004': Method 'Range' of ' object'_Global' failed
and this line is highlighted as the cause of the problem:
For Each c In Range("StaffName")
But I'm not sure why.
I just wondered whether someone may be able to look at this please and offer some guidance on how I may be able to solve this.
Many thanks and kind regards
I'm using the code below, which, in addition to creating a specific worksheet and copying and pasting a column header range, creates a named range called "Staff Name".
Option Explicit
Sub NamedRange()
Dim rMyRg As Range
Set rMyRg = Range([A1], [A1].End(xlDown))
ActiveWorkbook.Names.Add Name:="StaffName", RefersToR1C1:="=" & _
ActiveSheet.Name & "!" & rMyRg.Address(ReferenceStyle:=xlR1C1)
Worksheets.Add(After:=Worksheets(1)).Name = "Calculations"
Sheets("HMRC Project Data Download1").Select
Range("A1:H1").Select
Selection.Copy
Sheets("Calculations").Select
Range("B2").Select
ActiveSheet.Paste
End Sub
The codes works fine and the named range is successfully created
What I'm now trying to do is create a unique list from this named range using using VBA.
I've found a number of posts and tutorials which explains how to do this, one of which I've added below:
Sub listUnique()
Application.ScreenUpdating = False
Dim c As Range
Dim counter As Long
counter = 1
For Each c In Range("StaffName")
If WorksheetFunction.CountIf(Range("T:T"), c.Value) = 0 Then
Cells(counter, "T").Value = c.Value
counter = counter + 1
End If
Next c
Application.ScreenUpdating = True
End Sub
The problem I'm having with this, and all the other code examples I've used, is that when I run the code I receive the following error:
Run time error '1004': Method 'Range' of ' object'_Global' failed
and this line is highlighted as the cause of the problem:
For Each c In Range("StaffName")
But I'm not sure why.
I just wondered whether someone may be able to look at this please and offer some guidance on how I may be able to solve this.
Many thanks and kind regards