Microsoft Office Forums - View Single Post - Getting Object 1004 Error for a Dyamic Range

ScottyBee75 · 01-15-2025, 12:16 PM

I am getting an a runtime 1004 error when converting a static range to a dynamic range in my code. My code is located in a module. My workbook has two sheets in it. Below is my code. I have also attached the workbook file. Thanks for any help.

Code:

Sub AssignTasks()
    Dim namesRange As Range
    Dim tasksRange As Range
    Dim taskIndex As Integer
    Dim rowIndex As Integer
    Dim colIndex As Integer
    Dim taskCell As Range
    
    ' Define the ranges
    Set namesRange = Worksheets(1).Range("A2", Range("A2").End(xlDown))
    Set tasksRange = Worksheets(2).Range("A1", Range("A1").End(xlDown))
'    Set namesRange = Worksheets(1).Range("A2:A13")
'    Set tasksRange = Worksheets(2).Range("A1:A18")
    
    ' Initialize the task index
    taskIndex = 1
    
    ' Loop through the columns (dates)
    For colIndex = 2 To 11 ' B to K (2 to 11)
        ' Loop through the names
        For Each taskCell In namesRange
            ' Assign task to the cell
            taskCell.Offset(0, colIndex - 1).Value = tasksRange.Cells(taskIndex, 1).Value
            
            ' Update the task index
            taskIndex = taskIndex + 1
            
            ' Reset the task index if it exceeds the number of tasks
            If taskIndex > tasksRange.Rows.Count Then
                taskIndex = 1
            End If
        Next taskCell
    Next colIndex
End SubPrevent Duplicate Values in Columns or Rows.xlsm

Error 1004.jpg