Announcement

Collapse
No announcement yet.

Excel 2010 vba issues with code from 2003-2007

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    Code:
    Sub Insert_Blank_RowsV2()
    ' Stanley D. Grom, 07/29/2010, http://www.ozgrid.com/forum/showthread.php?t=144807
    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

    Code:
    Sub Insert_Blank_RowsV2()
    ' Stanley D. Grom, 07/29/2010, http://www.ozgrid.com/forum/showthread.php?t=144807
    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
    Code:
    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)

  • #2
    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?

    Comment


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

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

      S M C

      Click To Read: How To Use Tags In Your Threads/Posts
      Please take time to read Forum Rules before posting
      Message To Cross Posters

      Comment


      • #4
        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

        Comment


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

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

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

          JR

          Comment


          • #6


            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

            Comment

            Working...
            X