Greetings,
I would like to request some help in getting to the bottom of a rather puzzling problem regarding a macro I am trying to create in Excel 2007 which would highlight a range of cells down a column starting at Cell 6 and then select every 13th cell in every table I have created on the worksheet until they are all selected.
I record the macro with Relative References turned on, so that I can replicate the selection in the next columns over. Unfortunately, whether I run the macro in the original column or in another, I receive a 1004 run-time error with the message "application-defined or object-defined error". This error does not occur when Relative References is turned off.
Every time I record this macro and attempt to run it, it selects a certain range of cells and then abruptly stops. For each version that is recorded the cell it stops on changes. The macro I currently have recorded runs up to cell 27 and then crashes.
Here is the code for the macro:
It crashes at the line "ActiveCell.Offset(-26, 0).Range("A1,A14,A27,A40").Select" and I simply cannot figure out why. Any suggestions, advice or -- even better -- solutions to this baffling problem would be most appreciated.
Thank you,
Chris
I would like to request some help in getting to the bottom of a rather puzzling problem regarding a macro I am trying to create in Excel 2007 which would highlight a range of cells down a column starting at Cell 6 and then select every 13th cell in every table I have created on the worksheet until they are all selected.
I record the macro with Relative References turned on, so that I can replicate the selection in the next columns over. Unfortunately, whether I run the macro in the original column or in another, I receive a 1004 run-time error with the message "application-defined or object-defined error". This error does not occur when Relative References is turned off.
Every time I record this macro and attempt to run it, it selects a certain range of cells and then abruptly stops. For each version that is recorded the cell it stops on changes. The macro I currently have recorded runs up to cell 27 and then crashes.
Here is the code for the macro:
Code:
Sub Lang1()
'
' Lang1 Macro
'
'
ActiveSheet.Cells.Select
ActiveCell.Select
ActiveWindow.SmallScroll Down:=9
ActiveCell.Range("A1,A14").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-13, 0).Range("A1,A14,A27").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-26, 0).Range("A1,A14,A27,A40").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-39, 0).Range("A1,A14,A27,A40,A53").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-52, 0).Range("A1,A14,A27,A40,A53,A66").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=9
ActiveCell.Offset(-65, 0).Range("A1,A14,A27,A40,A53,A66,A79").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=15
ActiveCell.Offset(-78, 0).Range("A1,A14,A27,A40,A53,A66,A79,A92").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=9
ActiveCell.Offset(-91, 0).Range("A1,A14,A27,A40,A53,A66,A79,A92,A105").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=15
ActiveCell.Offset(-104, 0).Range("A1,A14,A27,A40,A53,A66,A79,A92,A105,A118"). _
Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-117, 0).Range("A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-130, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=15
ActiveCell.Offset(-143, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=15
ActiveCell.Offset(-156, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-169, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183").Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-182, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196"). _
Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=15
ActiveCell.Offset(-195, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209"). _
Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-208, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-221, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-234, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-247, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248,A261" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=15
ActiveCell.Offset(-260, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248,A261,A274" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-273, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248,A261,A274,A287" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-286, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248,A261,A274,A287,A300" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=18
ActiveCell.Offset(-299, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248,A261,A274,A287,A300,A313" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-312, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248,A261,A274,A287,A300,A313,A326" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-325, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248,A261,A274,A287,A300,A313,A326,A339" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(-338, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248,A261,A274,A287,A300,A313,A326,A339,A352" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
ActiveWindow.SmallScroll Down:=15
ActiveCell.Offset(-351, 0).Range( _
"A1,A14,A27,A40,A53,A66,A79,A92,A105,A118,A131,A144,A157,A170,A183,A196,A209,A222,A235,A248,A261,A274,A287,A300,A313,A326,A339,A352,A365" _
).Select
ActiveCell.Offset(13, 0).Range("A1").Activate
End Sub
Thank you,
Chris
Last edited: