Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Excel 2010 vba issues with code from 2003-2007

  1. #1
    Join Date
    17th July 2010
    Posts
    46

    Excel 2010 vba issues with code from 2003-2007

    I searched and came up with practically nothing.

    I have a handle on most of what needs modified in the code Stanley helped me with here http://www.ozgrid.com/forum/showthre...960#post515960

    However, if there is some advice on what else needs modified to work with XL2010 regarding the code below, I'd appreciate it; it would go far to nudging me in the right direction for modifying the rest of my code for 2 different projects I 'thought' I was close to completing. I would appreciate any advice or commentary on backwards-compatibility as well; if there are issues, I'm just going to make it for XL2010 and get the users upgraded. If there is a reference to 2010 'issues' I could get pointed to, that would be appreciated as well, as I'm not finding much that's very helpful. This is a HUGE step backward for me, given that we have limited XL2007 licenses and loaded up with XL2010 licenses.

    Thanks in advance!

    JR

    Original code submitted by Stanley
    VB:
    Sub Insert_Blank_RowsV2() 
         ' Stanley D. Grom, 07/29/2010, [URL]http://www.ozgrid.com/forum/showthread.php?t=144807[/URL]
        Dim rng As Range, cell As Range, LR As Long 
        Application.ScreenUpdating = False 
        LR = Cells(Rows.Count, "A").End(xlUp).Row 
        Range("A2:N" & LR).Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal 
        Columns("A:B").Insert 
        [A2] = 1 
        Columns(12).Copy Columns(2) 
        Set rng = Range([A3], [B65536].End(xlUp)(1, 0)) 
        With rng 
            .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C,R[-1]C+1)" 
            .Value = .Value 
        End With 
        Set cell = [A65536].End(xlUp) 
        cell(2) = 1 
        cell(2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cell - 1 
        Range([A2], [A65536].End(xlUp)).EntireRow.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo 
        Columns("A:B").Delete 
        Application.ScreenUpdating = True 
    End Sub 
    
    
    I got as far as correcting the syntax for XL2010 with this

    VB:
    Sub Insert_Blank_RowsV2() 
         ' Stanley D. Grom, 07/29/2010, [URL]http://www.ozgrid.com/forum/showthread.php?t=144807[/URL]
        Dim rng As Range, cell As Range, LR As Long 
        Application.ScreenUpdating = False 
        LR = Cells(Rows.Count, "A").End(xlUp).Row 
        Range("A2:N" & LR).Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal 
        Columns("A:B").Insert 
        Range("A2") = 1 
        Columns(12).Copy Columns(2) 
        Set rng = Range("A3", "B65536").End(xlUp)(1, 0) 
        With rng 
            .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C,R[-1]C+1)" 
            .Value = .Value 
        End With 
        Set cell = Range("A65536").End(xlUp) 
        cell(2) = 1 
        cell(2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cell - 1 
        Range("A2", "A65536").End(xlUp).EntireRow.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlNo 
        Columns("A:B").Delete 
        Application.ScreenUpdating = True 
    End Sub 
    
    
    (Note: I figured out the brackets are 'out' for 2010 and the basic range formatting that 2010 is anal about, but I'm getting errors [Application-defined or Object-defined 1004 error] here
    VB:
    Set rng = Range("A3", "B65536").End(xlUp)(1, 0) 
    
    
    I have a feeling it has to do something with this http://support.microsoft.com/kb/244435 . I tried searching this site for references I've read about making code compatible with future releases but could not find anything in a half-dozen passes)

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th July 2010
    Posts
    46

    Re: Excel 2010 vba issues with code from 2003-2007

    Ok. I figured out the syntax wanted '.Offset' (I think...it fixed the error). But this brings me back to the original problem I had prior: It 'sorts' but no longer inserts the blank rows.

    I think it's related to the R1C1 formula, as I don't see the code continuing down farther than row 3 in break mode while the column is inserted.

    Any thoughts?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,613

    Re: Excel 2010 vba issues with code from 2003-2007

    VB:
    Set rng = Range(Range("A3"), Range("B" & Rows.Count).End(xlUp).Offset(1, 0)) 
    
    

  4. #4
    Join Date
    17th July 2010
    Posts
    46

    Re: Excel 2010 vba issues with code from 2003-2007

    I attached a workbook with sample data and the formulae from Stanley in Module 1. I 'may' have misread his reply in that he intended that the 'Sub Z_Insert_Blank_Rows_DoesWork_000()' code was the working code as opposed to the 'Sub Insert_Blank_RowsV2()', but his code (V2) DID do the trick (at least in XL2007 on my system at work).

    I never did get the prior code (Sub_Z) working at all.

    JR

    Note: By default, the data is sorted descending in Column A (Aged Days), as that is the format exported out of our database AND is the desired outcome of the sorted groups. If I uploaded before resorting as noted, I apologize. However, it 'is' the 'reset' to put the data back to original format when playing with the code.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th July 2010
    Posts
    46

    Re: Excel 2010 vba issues with code from 2003-2007

    Thanks, SMC. However, I'm getting a 'Type mismatch' error on this line

    VB:
    cell(2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cell - 1 
    
    
    This line I've had trouble understanding regardless...

    JR

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    17th July 2010
    Posts
    46

    Re: Excel 2010 vba issues with code from 2003-2007

    I made changes to the other 2 lines of code reflecting your reference above for Rows.Count, but it still gives a type mismatch. Watching the code in break, I end up with #VALUE errors in cells that are supposed to be the result of the R1C1 formula, stopping at the line referenced with the 'Type mismatch' error. I don't see anything wrong with the R1C1 code, but then again...I'm still wrapping my head around it.
    JR

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Access denied Issues after Upgrading from Office 2003 to 2007
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: June 6th, 2009, 13:20
  2. Retain 2003 Code In 2007 And Back To 2003
    By ShortyTheDog in forum EXCEL HELP
    Replies: 4
    Last Post: August 30th, 2008, 00:09
  3. storing macros / patch issues Excel 2003
    By Nitro in forum Technical Issues Help
    Replies: 7
    Last Post: November 19th, 2005, 07:41

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno