asked on

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!

Norie

I can see a few problems with that code..

What are you trying to do?

byundt

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.

ste5an

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

Set cntNames = Sheet1.Range("A3")

makes no sense..

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

byundt

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.