VBA Object Error

Wm Allen Smith
Wm Allen Smith used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
NorieAnalyst Assistant

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

What are you trying to do?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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 Developer

Commented:
And: cntNames is defined as Integer not as Object or Range. So even

Set cntNames = Sheet1.Range("A3")

makes no sense..
Excel VBA Developer
Top Expert 2014
Commented:
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
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial