![]() |
#1
|
|||
|
|||
![]()
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 |
#2
|
|||
|
|||
![]()
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)) |
#3
|
|||
|
|||
![]()
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
![]() |
#4
|
|||
|
|||
![]()
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) |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
Marcia | Excel Programming | 2 | 06-13-2024 07:28 AM |
![]() |
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 |
![]() |
yonasan | Excel Programming | 3 | 06-12-2012 11:08 PM |