VBA Object Error

Hi, '
I am hoping that  you can help me with an Object error. When I run this code:

Sub Part_G()

    '
    Call EnterNames
   
    Dim rngNames As Range
    Dim cntNames As Integer
   
    With Range("A3")
    '----Put your code below this line----
   
  Dim A3 As Range
  Set rngNames = Sheet1.Range("A3")
  With Sheet1
        .Names.Add Name:="rngNames", RefersTo:=Range(rngNames.Offset(0, 0), _
            rngNames.End(xlDown))
   
 
    End With
    End With
   
   
With Range("A3")
  Set cntNames = Sheet1.Range("A3")
  With Sheet1
  cntNames = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
   
    MsgBox "Part G:  Number of names in the list = " & cntNames
    End With
End Sub

I get an object defined error on the "cntNames" variable . I am stumped. What am I missing?

Thanks!
Wm Allen SmithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
I can see a few problems with that code..

What are you trying to do?
byundtMechanical EngineerCommented:
You have two With blocks in succession, and the second one is for a worksheet with codename Sheet1. The error occurs because a worksheet doesn't have .Offset or .End properties or methods.
With Range("A3")
  Set cntNames = Sheet1.Range("A3")
  With Sheet1
  cntNames = Range(.Offset(1, 0), .End(xlDown)).Rows.Count

Open in new window


I don't know your worksheet layout, but this should work:
With Sheet1.Range("A3")
  Set cntNames = .Range("A3")
  cntNames = Range(.Offset(1, 0), .End(xlDown)).Rows.Count

Open in new window

In addition, you should Dim your row counter as Long, not Integer. Otherwise, you get an overflow error if there are more than 32,767 rows of data.
ste5anSenior DeveloperCommented:
And: cntNames is defined as Integer not as Object or Range. So even

Set cntNames = Sheet1.Range("A3")

makes no sense..
Glenn RayExcel VBA DeveloperCommented:
There are numerous issues with this code.  Fixing the assignment of this integer variable is only one.

Here is a revised version that determines the number of names and assigns a range name called "rngNames" to that range of text in column A.  Note that you don't need the With...End With options for any of the steps and are only recommended when acting on multiple objects.

Option Explicit
Sub Part_G()
    Call EnterNames
    
    Dim rngNames As Range
    Dim intNames As Integer
    
    '----Put your code below this line----
    Set rngNames = Sheet1.Range("A3", Range("a3").End(xlDown))
    Sheet1.Names.Add Name:="rngNames", RefersTo:=rngNames
    intNames = rngNames.Rows.Count - 1
    
    MsgBox "Part G:  Number of names in the list = " & intNames
End Sub

Open in new window


Regards,
-Glenn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
ste5an is correct--I goofed when including a statement saying:
Set cntNames = Sheet1.Range("A3")

Open in new window

I should have deleted that statement.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.