Error 1005 when recording macro using Relative References

[chrisbnz]

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:

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

 

[Andrew Poulsom]

Welcome to MrExcel.

Why do you need to do all that selecting? It's rarely necessary. The reason for the error is that the active cell is A14 and it can't be offset by -26 rows.

 

[chrisbnz]

Thanks for the reply. Essentially, what I am trying to do is create a foreign language study chart that calculates the minutes spent per day on certain tasks per language (I have seven language rows) and then uses this data to calculate things like the total hours spent per month, annually and so on with regard to specific languages and tasks.

My worksheet consists of a Totals sheet, and 12 other sheets for each month of the year. Each of the monthly sheets has a table allocated to each day of the month. The February sheet for instance contains 29 tables for 2012.

Each table has a number of columns headed "Reading", "Listening", "Writing", "Speaking", "Analysis" and "Scriptorium", and 7 rows headed with each language I am studying. There is an additional row calculating the total hours per task for that day, and an additional column calculating the total hours per language for that day.

What I have been doing is going down all the tables for each month, selecting all the Reading for Lang1 cells and saving them as a range under the Name Manager. Then I move on to Reading for Lang2 and so on until I have exhausted that column. I then repeat the process for all the columns, selecting Listening for Lang1, Listening for Lang2, and so on. I am saving each of these ranges and using this data in the Totals sheet to calculate things like "Hours spent on reading Lang1 in January", "Hours spent listening to Lang3 in March" and so on.

Because each table has seven language rows, plus six activity columns, and an additional total hours per day per language column and a total hours per day per activity row, creating ranges for all of this is extremely time consuming. The number of total ranges to be created in the Name Manager would be roughly something like ((7 columns * 8 rows) * roughly 30 days per month * 12 months of the year) which yields the ungodly total of 20160 ranges!!!

Thus, I hit upon the idea of creating a macro to handle all this selection for me, because the task is simply to daunting, laborious and, frankly, dead boring to do manually.

I'm not particularly adept at Excel, and this is the only way I can think to do this. By manually recording macros for the first column for each month (since the number of days per month differs), I would then reuse that macro across the remaining columns to get the selections and then save those ranges. I am saving them as things like January_Reading_Lang1, January_Reading_Lang2, February_Listening_Lang1, February_Listening_Lang2 and so on and so forth.

If you could offer any advice on how to do this, it would certainly make life a lot easier for me! I am hoping to having to these charts completed before the New Year. Doing this manually makes it all the more difficult, as you can probably imagine.

I can also provide a download to my Excel file as it is currently if that would help clarify things. I'm not at home at the moment, so I'll have to sort that out later.

Cheers,

Chris

 

[chrisbnz]

I just realized I made an error in the above post - it's not 20,000 ranges I need to save (thank God), that's the number of cells. D'oh. The number of ranges I need to save is thankfully much lower, but still in the thousands. In any case, doing this manually would still require me to select a total of 20,000 odd cells.

Unfortunately I can't edit my post anymore, but here is a screenshot of a dummy version of the charts I have at home:

 

[circledchicken]

Hi,

I can't see your screenshot, but in your original post is this what you want:

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

i.e. is there a purpose for including the SmallScroll, Offset etc. or is that just a product of the Macro Recorder?

 

[chrisbnz]

Hmm, that's odd, it should show. Here's the link: http://download1410.mediafire.com/8mn6pnp7142g/5nhw26wd7e9j1ec/lang_chart.JPG

Yes, all that stuff is just a by-product of the Macro Recorder. It does make the code rather ugly and untidy.

And thank you for that code snippet, that may just work! Is there any way to alter the column values without using Find and Replace? I'd rather not edit command code (such as changing "E" to "F" for example, which would inadvertently change "End Sub" to "Fnd Sub".)

If there is a way to run a macro with the code you provided, but using Relative References, that would be great. Then I could just manually add or remove cells in the code depending on how many I want selected. That would certainly beat having to do it manually.

EDIT: Just figured out a way: Search by selected text and replace that way.

Thanks,

Chris

 

[circledchicken]

Hi again,

I'm still not sure exactly what your trying to do (hopefully someone else might be), however, if for example you wanted to select a range consisting of cells starting from cell A1 and ending at A365 in steps of 13, you could do something like this:

Sub example()

' Define variables
Dim oRng As Range
Dim i As Long

Set oRng = Range("A1") ' A1 is the first cell in the range you want to select
For i = 14 To 365 Step 13 ' A14 is the next cell then continue in steps of 13 until row 365
    Set oRng = Union(oRng, Range("A" & i)) ' this builds the combined range required step-by-step
Next i
oRng.Select ' this selects the range built above

End Sub

 

[chrisbnz]

Hi circledchicken,

Thanks for your reply. As I mentioned in my second post, I'm trying to record data that measures time spent per language according to activity. Each sheet has a table allocated to a day of the month, and Language 1 in each table is separate by 13 cells. So I want to select all the Language 1 cells in the Reading column in each table and save that data so I can analyze how much reading I did in Language 1 for that month. The same applies for the rest of the language rows as compared against the other activity columns.

Basically, I want to be able to answer questions like "how much listening did I do to Language 3 in March" and "was this more than the amount of reading I did in Language 1 in January?". Things like that. In order to do this, I need detailed a analysis of all the rows and columns and how they relate to one another. The Totals sheet will handle this, as I will have tables totaling the amount of time spent on x activity for x language of x month, and then a Grand Totals table which totals the amount of time spent for the year, based on the aforementioned monthly tables.

Does that make sense? It's quite a complex task, and I think your first post may be the solution I was after. Thanks again!

 

[circledchicken]

Yes, it makes sense. However, I think you can organise your worksheets differently to achieve what you want using Pivot tables quite easily.

If you want to post a sample workbook with for example a couple of months sheets and a Total sheet I can see if I can help reorganise it in the way I think would be useful (you can post it here http://www.box.com/, for example and provide the link)

Also, just a note that the second code I posted does exactly the same thing as the first but slightly more generalised, so its more easily tweaked for different columns or using the active column or different ranges etc.

 

[chrisbnz]

http://www.filejumbo.com/Download/CD6E790524B9A20E

This is what I have so far. It's still pretty bare bones, but the basic structure of the tables is there. The only thing I haven't really got around to yet is the Totals sheet, because I need to sort out getting the data from the monthly sheets first.

I don't suppose you could think of some way to simplify what I'm trying to do?