Trouble with Automating Naming Ranges (runtime error 424)

bloodmilksky

Board Regular
Joined
Feb 3, 2016
Messages
202
Hi Guys,

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.



Code:
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
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi bloodmilksky,

The method that you're using to create names should work.

The Object Required Runtime Error 424, implies there's a problem with the reference to OutRange - typically this happens when the variable is set to Nothing.

I don't see anything in your code that would cause OutRange to lose the assignment to Sheets("Holidays_Requested").Range("B2"), so that might take some debugging to trace why that is happening.

Do you have any event code, such as Worksheet_Change the might be getting triggered when dataout values are written to the worksheet?
 

Forum statistics

Threads
1,085,495
Messages
5,384,002
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top