[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Help in migration from Older office versions to Office 2010- Excel, Word, Access

Posted on 2011-10-20
15
Medium Priority
?
343 Views
Last Modified: 2012-06-21
Basically I am migrating many applications which has Macros Written in Office 2003 to Office 2010.  The MIcrosoft office Compatibility check inspector (OCCI) produces a report of the VBA code lines which has been changed, removed, deprecated. I need to make the code work by making changes which is compatble to 2010. I am very new in this field and really puzzled how to do. After the solution to the above please suggest me the right place where i can post my questions and get answers fast.
0
Comment
Question by:VBAlearner2010
  • 9
  • 5
15 Comments
 

Author Comment

by:VBAlearner2010
ID: 36998881
Could anyone please help me in sorting why the below code does not run in Office 2010. It gives an error "Subtotal Method of Range Class failed" and the line of error is,

Selection.Subtotal GroupBy:=9, Function:=xlSum, TotalList:=Array(12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

ub Macro() 
     '
     ' '
    Columns("L:L").Select 
    Selection.Insert Shift:=xlToRight 
    Range("L1").Select 
    ActiveCell.FormulaR1C1 = "Name" 
    Range("L2").Select 
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""s"",-RC[-1],RC[-1])" 
    Selection.AutoFill Destination:=Range("L2:L2980") 
    Range("L2:L2980").Select 
    Columns("K:L").Select 
    Selection.Style = "Comma" 
    Range("I7").Select 
    Range("A1:T2980").Sort Key1:=Range("I7"), Order1:=xlAscending, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    Selection.Subtotal GroupBy:=9, Function:=xlSum, TotalList:=Array(12), _ 
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True 
End Sub 

Open in new window

0
 
LVL 12

Assisted Solution

by:viralypatel
viralypatel earned 1000 total points
ID: 36998934
there could be some dummy or not required rows with invalid values in the specified range. Remove those rows and it will work fine.
0
 

Author Comment

by:VBAlearner2010
ID: 36998983
When i run the code in debug mode i see the I7 selected which has no data. The cell is empty. Will it be the reason....?

But the data is present only from range---- L2:L2980

I am really puzzled what it does because i dont know what the application does but i need to make the code work.....
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37000518
I7 is supposed to be the sort key,  This should help you understand better:

MSDN Excel.Range.Sort Page
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37000537
It is the cell that should be telling the sort function what range to sort.  Something is supposed to be in that cell.
0
 

Author Comment

by:VBAlearner2010
ID: 37000774
I changed the code as follows... But it does not gives me and error.. But it executes to add hypens in the L row untill the range specified.. But i am really puzzled without knowing its functionality.

Changed Code:

With Range("A1:T2980")
   .Subtotal GroupBy:=9, Function:=xlSum, TotalList:=Array(12), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With

Open in new window



Could you please see the first post of mine and explain me what the code does by seeing the code.. Because i am not an expert in knowing from the code.. Any help would be very great for me.....
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37001101
One of the best things you can do to learn to understand this type of code is to open it in VBA, and then press F8 to step through the code.  If you hover over any item in the yellow line it tells you what the value is currently, and you can watch it step though each line,  and see what it does on the spreadsheet.  Very useful technique.

Sub Macro() 
     '
     ' '

    Columns("L:L").Select                     ' Selects Worksheet Column "L" The entire thing.
    Selection.Insert Shift:=xlToRight      '- inserts a column to the left of Column "L"
    Range("L1").Select                          '- Selects Cell L1
    ActiveCell.FormulaR1C1 = "Name" '-Sets L1 to "Name"
    Range("L2").Select                          '-Selects Cell L2
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""s"",-RC[-1],RC[-1])"  '- Sets Cell L2 to "IF(RC[-5....
    Selection.AutoFill Destination:=Range("L2:L2980")  'Copies the formula down to L2980
    Range("L2:L2980").Select                 '-Selects the range of cells L2:L2980
    Columns("K:L").Select                       'Extends the selection to include Column K -K2:L2980
    Selection.Style = "Comma"                'puts those both to comma format cell formating.
    Range("I7").Select                             '-Selects I7
    Range("A1:T2980").Sort Key1:=Range("I7"), Order1:=xlAscending, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal               '-This is a big sort.  It sorts the table A1:T2980 based on the 
                                                               ' values in column i  (I don't sort a lot, so not positive on this)
    Selection.Subtotal GroupBy:=9, Function:=xlSum, TotalList:=Array(12), _ 
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True 
    '-This last step subtotals the values in the table.  
End Sub

Open in new window


Hope that helps!
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37001176
Check this out!  It should help you understand it!

MSDN Subtotal Reference
0
 

Author Comment

by:VBAlearner2010
ID: 37005004
I've requested that this question be closed as follows:

Accepted answer: 0 points for VBAlearner2010's comment http:/Q_27406310.html#36998881

for the following reason:

It helped me in understanding what the code does.
0
 

Author Comment

by:VBAlearner2010
ID: 37005005
Thank you for the very nice explanatio @scriptaddict. I understood the code flow. I use F8 and step into the code always..

But some times i dont understand complex statements which stucks me inbetween..... So i would post more things what i dont understand and get it clarified in this post...  I would be having more doubts in the following days.. it will be good if i have it in this common post and specific errors in different posts.....
0
 

Author Comment

by:VBAlearner2010
ID: 37005210
Another clarification,

The previous code i have pasted contains this line

ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""s"",-RC[-1],RC[-1])"  ---> Can anyone explain me this line?

0
 
LVL 11

Assisted Solution

by:ScriptAddict
ScriptAddict earned 1000 total points
ID: 37006828
This sets the value of the Active Cell to "=IF(RC[-5]=""s"",-RC[-1],RC[-1])"

There are two types of references in Excel,  One is A1 and is the default reference.  The second is R1C1.  This formula uses R1C1 notation.  For more information check it out here:

A1 R1C1 in more detail
0
 

Accepted Solution

by:
VBAlearner2010 earned 0 total points
ID: 37016405
@scriptAddict : Thank you very much for the explanation. It was clear...
0
 

Author Closing Comment

by:VBAlearner2010
ID: 37061177
Thanks for the supports in clearing my doubts
0
 

Author Comment

by:VBAlearner2010
ID: 37045094
Thanks for the help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question