VBA Macro For Naming Ranges

Question

I am trying to use the below to run through values in Column A on a Sheet Named "Report" and Create these ranges in a Sheet called "Holidays_Requested" but everytime I it pops up with

Object Required Runtime error 424.

Can anyone help or know of an alternative way of creating named ranges using VBA.

Sub TransposeRange_new_code()
    Dim OutRange As Range
    Dim x As Long, y As Long
    Dim sKey As String
    Dim maxCount As Long
    Dim data, dic, keys, items, dataout()


    Application.ScreenUpdating = False
    data = Sheets("Report").Range("A2:E" & Report.Cells(Report.Rows.Count, "A").End(xlUp).Row).Value2


    Set dic = CreateObject("scripting.dictionary")
    Set OutRange = Sheets("Holidays_Requested").Range("B2")


    For x = 1 To UBound(data, 1)
        If Trim$(data(x, 1)) <> "_" Then
        sKey = Trim$(data(x, 1)) & Chr(0) & Trim$(data(x, 2))
        If Not dic.exists(sKey) Then dic.Add sKey, CreateObject("Scripting.Dictionary")
        dic(sKey).Add x, Array(data(x, 4), data(x, 5))
        If dic(sKey).Count > maxCount Then maxCount = dic(sKey).Count
        End If
    Next


    ReDim dataout(1 To maxCount + 1, 1 To dic.Count * 3)
    keys = dic.keys
    items = dic.items
    For x = LBound(keys) To UBound(keys)
        dataout(1, x * 3 + 1) = Split(keys(x), Chr(0))(0)
        dataout(1, x * 3 + 2) = Split(keys(x), Chr(0))(1)
        For y = 1 To items(x).Count
            dataout(1 + y, x * 3 + 1) = items(x).items()(y - 1)(0)
            dataout(1 + y, x * 3 + 2) = items(x).items()(y - 1)(1)
        Next y
    Next




    OutRange.Resize(UBound(dataout, 1), UBound(dataout, 2)).Value2 = dataout


    For x = 1 To UBound(keys)
        OutRange.Offset(0, (x - 1) * 3).Resize(maxCount, 2).Name = "" & validName(Split(keys(x - 1), Chr(0))(0))
        With OutRange.Offset(0, (x - 1) * 3 + 1)
            .Hyperlinks.Add anchor:=.Cells(1), Address:="mailto://" & .Value2, TextToDisplay:=.Value2
        End With
    Next


End Sub

where are you trying to set a `Named Range` ? what line ? what variable you intended to use for the `Named Range` ? — Shai Rado, Apr 19 '17 at 14:42
I believe it is Application.ScreenUpdating = False data = Sheets("Report").Range("A2:E" & Report.Cells(Report.Rows.Count, "A").End(xlUp).Row).Value2 Set dic = CreateObject("scripting.dictionary") — bloodmilksky, Apr 19 '17 at 14:48
Which line specifically? You've given *three* lines, but the error can only raise on one of them. I suspect it's the call to `Report.Rows`, unless `Report` object is globally-scoped and instantiated in another routine. See my answer below. — David Zemens, Apr 19 '17 at 15:01

score 2 · Answer 1 · edited Sep 21 '17 at 16:16

In your code, you're referring to a non-instantiated variable Report. Since this variable hasn't been declared with a Dim statement, it will be treated as an empty variant, zero-length string, or 0-value numeric, or a Nothing object, depending on how/when you call upon it.

And since you're doing Report.__something__ the compiler assumes it's supposed to be an Object (since only Object type have properties/methods). Since it doesn't exist and/or hasn't been assigned, you're doing essentially: Nothing.Cells...

This will always raise a 424 because in order to invoke any .__something__ call, you need to invoke it against a valid, existing Object.

Change:

data = Sheets("Report").Range("A2:E" & Report.Cells(Report.Rows.Count, "A").End(xlUp).Row).Value2

To:

With Sheets("Report")
    data = .Range("A2:E" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value2
End With

As always, using Option Explicit in each module will prevent you from executing/running code with undeclared variables. I would recommend adding that statement at the top of each code module, and then rectifying any compile errors (such as Variable undefined) which might arise.

Also: See here for more reliable ways of finding the "last" cell in a given range.

And here is a VB.NET (similar conceptually) explanation of why you should be using Option Explicit.

VBA Macro For Naming Ranges

1 Answers1