VBA Runtime Error 1004 “Application-defined or Object-defined error” when trying to assign value to a variable

Question

1st i explain what i'm trying to do. I have a workbook with many sheets and each of them have many named ranges. I want to loop through named ranges on a specific sheet and then hide/unhide them, if it has a specific name. At start i want to isolate the sheet and there i have the problem. I'm trying to get the name of the sheet, the range is on and assign that name to a variable. Here is the part of the code, i'm having problems with:

Dim rng as Name
Dim shP as String
for each rng in ThisWorkbook.Names
shp = rng.RefersToRange.Parent.Name 'here i get the error
... rest of the code ...

If i just Debug.Print rng.RefersToRange.Parent.Name instead in that line, i get the sheet name printed out in immediate window. I also tried to Dim shP as Variant but it didn't helped.

Thank you in advance for your answers.

Gary's Student · Accepted Answer · 2020-05-06 11:54:22Z

2

You probably have a Name in the workbook that does not correspond to a block of cells. Here I assign the Name when to the formula =now()

If I run:

Sub WhatsInaName()
    Dim nm As Name, s As String

    For Each nm In ThisWorkbook.Names
        MsgBox nm.RefersToRange.Parent.Name
    Next nm

End Sub

I also get a 1004

You should first run:

Sub listum()

    With ActiveWorkbook
        If .Names.Count > 0 Then
            For i = 1 To .Names.Count
                MsgBox (i & "  " & .Names(i).Name)
            Next
        End If
    End With
End Sub

to verify each Name is a cell block.

answered May 6, 2020 at 11:54

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ale Over a year ago

Yes, you identified the problem correctly. One range lost it's cell block. Is there a way to check and skip those faulty ranges? I don't quite understand the point of your last code block.

Gary's Student Over a year ago

@Ale I would temporarily use some OnError logic to skip the "bad" Names ...........................in the long run you should remove them!

Ale Over a year ago

Ok, thx. In the long run all ranges will be proper. Just now, while i work on that file some of them may break. I think, i got my answer now.

Damian · Accepted Answer · 2020-05-06 10:21:52Z

0

If you are getting a worksheet, then you need to use a Worksheet object.

Option Explicit
Sub Test()

    Dim rng As Name
    Dim shP As Worksheet
    For Each rng In ThisWorkbook.Names
        Set shP = rng.RefersToRange.Parent 'here i get the error
    '... rest of the code ...
    Next rng

End Sub

Then you can use the shP as if it were a worksheet.

answered May 6, 2020 at 10:21

Damian

5,1741 gold badge12 silver badges22 bronze badges

2 Comments

Ale Over a year ago

If i do that, i get the same error. I just want to get the name of the sheet, so i can compare it later on.

Damian Over a year ago

This worked for me with no errors, to get the name you just need to use shP.Name whenever needed

Collectives™ on Stack Overflow

VBA Runtime Error 1004 “Application-defined or Object-defined error” when trying to assign value to a variable

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related