Error 1005 when recording macro using Relative References

C

chrisbnz

New Member
Joined
Dec 21, 2011
Messages
12
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:
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
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
 
Last edited:
Sort by date Sort by votes
Yeah, that must've been it. Now it's sorted! I also found some code that deletes outdated pivot table data from the filter list, since the language filter still displayed outdated entries that were previously deleted.

Merry Christmas to you too and a happy new year! :)

Now I sleep.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
VBA to format border on every nth cell
Replies
2
Views
1K
ExceL0ver
E
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
D
  • Locked
  • Question Question
IRR Error
Replies
1
Views
479
Fluff
Fluff
D
  • Question Question
IRR error
Replies
1
Views
654
Joe4
Joe4
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
260
Fluff
Fluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top