Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-15-2025, 12:16 PM
ScottyBee75 ScottyBee75 is offline Getting Object 1004 Error for a Dyamic Range Windows 10 Getting Object 1004 Error for a Dyamic Range Office 2019
Novice
Getting Object 1004 Error for a Dyamic Range
 
Join Date: Nov 2023
Posts: 9
ScottyBee75 is on a distinguished road
Default Getting Object 1004 Error for a Dyamic Range

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

Reply With Quote
  #2  
Old 01-15-2025, 03:32 PM
batman1 batman1 is offline Getting Object 1004 Error for a Dyamic Range Windows 11 Getting Object 1004 Error for a Dyamic Range Office 2013
Advanced Beginner
 
Join Date: Jan 2025
Posts: 57
batman1 is on a distinguished road
Default

This is because when the code is run, Worksheets(1) is active, so Range("A1").End(xlDown) means a cell on Worksheets(1), so
Worksheets(2).Range("A1", <cell on Worksheets(1)>)
causes an error.

Write code that is independent of the active sheet, e.g.

Code:
    Set namesRange = Worksheets(1).Range("A2", Worksheets(1).Range("A2").End(xlDown))
        Set tasksRange = Worksheets(2).Range("A1", Worksheets(2).Range("A1").End(xlDown))
Reply With Quote
  #3  
Old 01-15-2025, 05:55 PM
ScottyBee75 ScottyBee75 is offline Getting Object 1004 Error for a Dyamic Range Windows 10 Getting Object 1004 Error for a Dyamic Range Office 2019
Novice
Getting Object 1004 Error for a Dyamic Range
 
Join Date: Nov 2023
Posts: 9
ScottyBee75 is on a distinguished road
Default

Thanks Batman1, your solution worked out great. I spent several hours looking at my code and would not have seen that so I learned something new
Reply With Quote
  #4  
Old 01-16-2025, 01:15 AM
batman1 batman1 is offline Getting Object 1004 Error for a Dyamic Range Windows 11 Getting Object 1004 Error for a Dyamic Range Office 2013
Advanced Beginner
 
Join Date: Jan 2025
Posts: 57
batman1 is on a distinguished road
Default

Yes, remember that Range("A1").End(xlDown) is really ActiveSheet.Range("A1").End(xlDown) = Worksheets(1).Range("A1").End(xlDown)
= cell on Worksheets(1)

=> Worksheets(2).Range("A1", <cell on Worksheets(1)>) throws an error because you can't use a cell on Worksheets(1) to specify a range on Worksheets(2)
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting Object 1004 Error for a Dyamic Range Run Time error 1004 Marcia Excel Programming 2 06-13-2024 07:28 AM
Getting Object 1004 Error for a Dyamic Range VBA error 1004 Application-defined or object-defined error petedacook Excel Programming 1 10-27-2022 01:22 PM
Suddenly having trouble using .Refresh BackgroundQuery. Error 1004, General ODBC Error PLSBRYDOBOY Excel Programming 0 08-30-2019 12:17 PM
Run Time Error '1004' galkej Excel 0 02-03-2014 06:39 AM
Getting Object 1004 Error for a Dyamic Range Run time error 1004 yonasan Excel Programming 3 06-12-2012 11:08 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:19 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft