Trouble with Automating Naming Ranges (runtime error 424)


Board Regular
Feb 3, 2016
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.

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

    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

    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

End Sub

Jerry Sullivan

MrExcel MVP
Mar 18, 2010
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

Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...