• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 890
  • Last Modified:

Converting Excel 2003 to 2010 - runtime error caused by Selection.Insert Shift:=xlToRight

We have an Excel 2003 spreadsheet that contains a macro.  We are upgrading Office to 2010.  When we convert the file, one of the macros generates a runtime 1004 error and when I open the debugger, The line 'Selection.insert Shift:=xlToRight' is highlighted.  Why would this work in 2003 but not 2010,  Is there a way to fix this?  I know nearly nothing about macros and VBA.  I have attached the code.
inventory-macro.txt
Snap5--Small-.bmp
Snap6--Small-.bmp
0
tmaususer
Asked:
tmaususer
  • 8
  • 8
1 Solution
 
ExcelGuideConsultantCommented:
Actually works fine here....Im using Excel 2010.

Maybe you can try to replace that code (and the line above) to (you could do this for both, but you will need to change the line a bit, e.g. other columns)
Sheet1.Columns("C:F").Insert Shift:=xlToRight

of course sheet1 need to be edited according to your file.
0
 
redmondbCommented:
Hi, tmaususer.

Excel often loses track of the actual last row/column in a sheet and I suspect that this is what's happening here. So we need Excel to recalculate the correct last column.

Immediately before the following line...
Sheets("Launch").Activate
... please insert the following line...
if Sheets("Weekly Breakdown").UsedRange.Rows.Count < 0 then Debug.Print "?"

("UsedRange.Rows.Count " can never be negative so this condition will never be True. However merely calculating the Count forces Excel to correctly determine the last row and column.)

Regards,
Brian.
0
 
tmaususerAuthor Commented:
Brain,
I still got the error.  Would I also need to do something similar for the line below:
Sheets("Weekly Breakdown").Activate

Psychotec, I am trying your suggestion now.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tmaususerAuthor Commented:
Psychotec,
I am running your suggestion now.  I was wondering though if I need to do something like that for the lines below?

Columns("G:AZ").Select
    Range(Selection, Selection.End(xlToRight)).Select
0
 
redmondbCommented:
Thanks, tmaususer.

Perhaps the sheet is effectively full. At the time the error occurs, what's the last column in that sheet (<CTRL-END> will get you there)?

Regards,
Brian.
0
 
tmaususerAuthor Commented:
The first two suggestion don't seem to help.  I have attached a picture of the bottom of the column (row 1364) where the macro stops.  The last column is labeled 'AR'.
col--Small-.bmp
0
 
redmondbCommented:
tmaususer,

(1) Did you identify "AR1364" by hitting <CTRL-END>?

(2) Near the start of the macro, it deletes a number of columns. Roughly how many would you expect?

Thanks,
Brian.
0
 
tmaususerAuthor Commented:
When I hit CTRL-END, I am taken to 4485,XFD
0
 
redmondbCommented:
tmaususer,

XFD is the last column so the spreadsheet is full and therefore you can't insert any more columns.

If the cause of this isn't immediately clear to you, can you post a copy of that sheet file here, please?

(BTW, the last row is also a lot further down than you thought.)

Thanks,
Brian.
0
 
tmaususerAuthor Commented:
The sheet has confidential material on it. Is there something I could tell someone (where I work who is familiar with VBA or macros) to try?
0
 
redmondbCommented:
tmaususer,

You could try the following...
(1) First let's check you're correctly set-up. Select a few cells with values in them. Can you see a Count value on the bottom line of the Excel window? If not, let me know, otherwise...
(2) Scroll to the right until you find a column with no data in row 1. Lets say it's column ZZ.
(3)  Key something into cell ZZ1.
(4) Select column ZZ by clicking on its header.
(5) Select all of the columns from ZZ to the end by holding down SHIFT and CTRL and clicking once on the Right Arrow.
(6) Is there a Count value on the bottom line? If not then there was no data in columns ZZ to XFD. Delete those columns, save the file, close it and re-open it. Now do <CTRL-END> - where do you end up?
Alternatively, if there is a Count value then that means that there is data after the end of the header row. You'll just have to check it out and see if it's valid. If it is then you've blown Excel's capabilities (16,384 columns) - either drop historical data or a re-design is in order. Oh, now quit out of the file (you changed ZZ, remember?!).

Regards,
Brian.
0
 
tmaususerAuthor Commented:
I followed your instructions, using column AZ as the last.  I then closed and reopened and did a CTRL-END.  I was taken to cell 2884, T.  I also ran the macro, but got the same error.  I will see if I can get permission to perhaps email the file to you.
0
 
redmondbCommented:
tmaususer,

Well we seem to be on the right track. However, digging in to the macro there's a lot of copying and renaming going on - perhaps another sheet also has excess columns.

So, please go through each sheet and hit <CTRL-END> and make a note of any whose last active column is greater than, say, ZZ. For each of these sheets repeat the steps outlined in my previous post. (Don't forget to save, close and re-open the file before retrying the macro.)

Thanks,
Brian.
0
 
tmaususerAuthor Commented:
On the rest of the sheets, CTRL-END took me to cells with values. The farthest column of any sheet was AR.  However, there was one sheet with 27962 rows.
0
 
redmondbCommented:
tmaususer,

Thanks. The rows don't matter. The issue is that somewhere along the line, large numbers of blank columns are added to one or more sheets.

I'm going to change the macro to output sheet sizes as it's running. Are you familiar with Debug.Print and/or the Immediate pane?

Edit: Please see attached. I've made two sets of changes to InventoryReport (now renamed InventoryReport_Debug)...
(1) At the start, each sheet's last cell is reset.
(2) At various stages, a sheet's last column is captured. These are displayed in one or more messages immediately before the error and they're also Debug.Print'ed. Please copy the Debug.Print output or the message(s) and post here.

Thanks,
Brian.inventory-macro-V2.xlsm
0
 
tmaususerAuthor Commented:
Thank you for your help.  It sounds like your last response would have definitely worked.  Sorry for the long delay.  I was tending to an ill family member the past few weeks.  Our programmer circumvented the problem by writing a .NET app to replace the spreadsheet.
0
 
redmondbCommented:
Thanks, tmaususer. I hope all is well with your family member.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now