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
Hi chrisbnz,

Attached is an example of how I might set it up:
http://www.box.com/s/yx2ltc6bv9rajozmg587

All the monthly sheets are now consolidated into 1 - you can still view only January for example, or only 01/01/2012 etc. by using the filters.

The 'Summary' sheet then has a Pivot table. These are quite flexible so you can amend the Layout/show Subtotals/Grand Totals etc. and drill down into the data in many different ways. Here's an introduction to Pivot tables:
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

Once you have set up the Pivot table(s) as you want (you can drag and drop different fields/change options so it looks like you want it to), you then just need to click the refresh button on the tables to update it when you've put in the data in the 'Data' tab.
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi circledchicken,

Thanks immensely for this. I knew there had to be a less clunky way of doing what I was trying to do, and although I suspected a pivot table would probably be a solution, I wasn't quite sure how exactly to set it up. This is clearly a much more efficient way of obtaining and analyzing data. I mean why do it in twelve sheets if you can condense it all down into one, right?

I guess I can delete the About sheet now, since the issue of hard-coded cell ranges is now no more.

I think the way it's set up now is pretty much how I'll keep it, but I'll have a play around with it and see what else I can do.

Thanks once again for doing that it, it's really appreciated. :)
 
Upvote 0
I've been playing around some more with these sheets and just have a couple of questions if that's alright:

1) is there a way that I could specify the number of language rows? I'm planning to share these once they're finished, so others will undoubtedly have different goals, some involving more or less than the seven I have in place.

2) is there a more efficient way to specify the language names than using Find and Replace? When I share them eventually, I'll probably have default names like Lang1, Lang2 ect. in the Language column, so I need a way to overwrite that without too much effort. I had considered using some blank cells to input the names, and then coding a reference to that cell from the appropriate cell in the Language column.

Cheers,

Chris
 
Upvote 0
Hi Chris,

You could use the following VBA code for both questions.

The user would enter the languages they want in column M (starting in M2 downwards) and then run this code to generate a new dataset (you could attach the code to a button).
The pivot table source is also updated to reflect the new dataset.

Code: 
Option Explicit
 
Sub example()
 
Dim i As Long
Dim j As Long
Dim dteStart As Long
Dim dteEnd As Long
Dim dteCurr As Long
Dim rngLng As Range
 
dteStart = DateValue("01/01/2012") ' start date
dteEnd = DateValue("31/12/2012") ' end date
dteCurr = dteStart ' current date
 
With Sheets("Data")
    .Range("A1").CurrentRegion.Offset(1, 0).Clear ' clear existing data
    Set rngLng = .Range("M2", .Range("M2").End(xlDown)) ' languages range
    j = WorksheetFunction.CountA(rngLng) ' no.of languages
    i = 2 ' first data row
 
    Do Until dteCurr = dteEnd + 1
        .Range("A" & i).Resize(j) = dteCurr ' date
        .Range("A" & i).Resize(j).NumberFormat = "dd/mm/yyyy" ' number format
        .Range("B" & i).Resize(j) = Application.Text(dteCurr, "mmmm") ' month
        .Range("C" & i).Resize(j) = Application.Text(dteCurr, "dddd") ' day
        .Range("D" & i).Resize(j) = rngLng.Value ' languages
        .Range("K" & i).Resize(j).Formula = "=SUM(RC[-6]:RC[-1])" ' total
        dteCurr = dteCurr + 1
        i = i + j
    Loop
End With
 
' update PivotTable source range
Sheets("Summary").PivotTableWizard SourceType:=xlDatabase, SourceData:="Data!R1C1:R" & i - 1 & "C11"
 
End Sub
 
Upvote 0
Thanks, although unfortunately I'm getting that 1004 application or object-defined error again when I try to run the code. I attached it to a button, attempted to run it after entering content in M2 onwards and the macro crashes at the following line:

Code: 
.Range("A" & i).Resize(j) = dteCurr ' date
Too bad my Visual Basic skills are non-existent. :(

EDIT:

Actually, I got it working now. It appears the error was caused by an additional column I added that converts the total minutes to hours. I'll see if I can add that to the code somehow, based on what you've written. Now that I've actually looked through it I can follow it more or less, I'm just not very familiar with Visual Basic syntax (it's been a long, long time since I last used Basic in any form).

Cheers,

Chris
 
Last edited:
Upvote 0
Could you perhaps edit the total formula to "=SUM(RC[-6]:RC[-1])/60"
Unless your trying to do something different.
 
Last edited:
Upvote 0
I had considered something like that, but I'm keeping the total minutes column, so there's just an additional total hours column in column L.

I tried altering the code in the loop to something like this:

Code: 
Do Until dteCurr = dteEnd + 1
        .Range("A" & i).Resize(j) = dteCurr ' date
        .Range("A" & i).Resize(j).NumberFormat = "dd/mm/yyyy" ' number format
        .Range("B" & i).Resize(j) = Application.Text(dteCurr, "mmmm") ' month
        .Range("C" & i).Resize(j) = Application.Text(dteCurr, "dddd") ' day
        .Range("D" & i).Resize(j) = rngLng.Value ' languages
        .Range("K" & i).Resize(j).Formula = "=SUM(RC[-6]:RC[-1])" ' total
        .Range("L" & i).Resize(j).Formula = "=SUM(RC[-7]:RC[-2])/60" ' total
        dteCurr = dteCurr + 1
        i = i + j
    Loop
The last line summing the same rows as the column K, and simply dividing the result by 60. The range is -2, so as to skip over the minutes column in K. But still I get that pesky 1004 error and it crashes on the first line of the loop.

I guess the simplest solution would be simply to delete the total minutes column and replace it with total hours. Either way, the whole thing is working out much better than it was a couple of days ago, that's for sure.:biggrin:
 
Upvote 0
Hi,

The reason is if you add an data next to column M (in O) then this line:

Code: 
.Range("A1").CurrentRegion.Offset(1, 0).Clear ' clear existing data

deletes the list of languages too and so you get the error. The solution would be to move the language list to column N for example and it should work fine (just keep an empty column between the main data table and the list of languages).

So the lines you need to update would be the language range:
Code: 
    Set rngLng = .Range("[B]N[/B]2", .Range("[B]N[/B]2").End(xlDown)) ' languages range

and the Pivot table source to include the extra column:
Code: 
Sheets("Summary").PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:="Data!R1C1:R" & i - 1 & "C[B]12[/B]"
 
Upvote 0
As far as I can tell, it's working perfectly now. Hurray. There was a little glitch with the last line of the code (the one that updates the pivot table), as it seemed to generate an extra pivot table on the Summary sheet with some blank fields. So what I did was enter the following code in VB for the Summary sheet:

Code: 
Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
End Sub

And now it automatically updates without a hitch. Which is nice. Hopefully that's the last of it.

Thanks again for your help circledchicken. Without you, I'd still be churning out thousands of manually created cell ranges for hundreds of tables! :eek:

Best regards,

Chris
 
Upvote 0
Great! - your welcome.

For that last bit it shouldn't generate the extra Pivot Table - that probably means it's not updating the source for the one you need it too (even with your additional refresh code):

Code: 
Sheets("Summary").PivotTables("[B]PivotTable1[/B]").PivotTableWizard SourceType:=xlDatabase, SourceData:="Data!R1C1:R" & i - 1 & "C11"

You need to change "PivotTable1" to the name of your PivotTable in the code above so the source is updated correctly - the name might have changed if you deleted and added re-added the Pivot Table or some other reason (it might depend on your Excel version but I think you can right click and select Pivot Table Options and find the name there).

You should still keep the extra code you added too.

Have a great Christmas / new year!
 
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
672
ExceL0ver
E
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
897
NdNoviceHlp
NdNoviceHlp
D
  • Locked
  • Question
IRR Error
Replies
1
Views
294
Fluff
Fluff
D
  • Question
IRR error
Replies
1
Views
418
Joe4
Joe4
Z
  • Question
Attrition forecast for next 3 months?
Replies
0
Views
408
zinah
Z

Forum statistics

Threads
1,215,455
Messages
6,124,935
Members
449,195
Latest member
Stevenciu

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