Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Excel Macro works in Office 2007 but not Office 2000

Following code works in Excel 2007 but throws an error in Excel 2000.  Could any of you VB masters help me make this backwards compatible?  Thanks.
Sub Trend()
'
'
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim iLoop As Integer 'number of facilities
Dim i As Integer 'loop count
Dim of As Integer 'offset to new facility

    'Disable screen updating
    Application.ScreenUpdating = False
    
iLoop = WorksheetFunction.CountIf(Columns(7), "Site:")
 For i = 0 To iLoop - 1
of = (i * 97)

    Range("R11").Select
    ActiveCell.Offset(of, 0).Select
    Selection.ClearContents
    Range("B11:F12").Offset(of, 0).Select
    
        With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlLTR
        .MergeCells = False
    End With
    Range("B11").Select
    ActiveCell.Offset(of, 0).Select
    Selection.ClearContents
    Range("G11").Select
    ActiveCell.Offset(of, 0).Select
    Selection.ClearContents
    Range("H11:O11").Offset(of, 0).Select
    
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("H11").Select
    ActiveCell.Offset(of, 0).Select
    Selection.Cut
    Range("C11").Select
    ActiveCell.Offset(of, 0).Select
    ActiveSheet.Paste
    Rows(13 + of & ":" & 45 + of).Select
    Selection.EntireRow.Hidden = True
    Rows(48 + of & ":" & 59 + of).Select
    Selection.EntireRow.Hidden = True
    Rows(61 + of & ":" & 61 + of).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(61 + of & ":" & of + 61).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(63 + of & ":" & of + 82).Select
    Selection.EntireRow.Hidden = True
    Rows(84 + of & ":" & of + 84).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(84 + of & ":" & of + 84).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(86 + of & ":" & of + 106).Select
    Selection.EntireRow.Hidden = True
    Range("C61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "Rented/Occupied"
    Range("C62").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "Length of stay (mos)"
    Range("C84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "Avg/Rent/Unit"
    Range("C61").Offset(of, 0).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlLTR
        .MergeCells = False
    End With
    Range("C62").Offset(of, 0).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlLTR
        .MergeCells = False
    End With
    Range("H61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C[-1]/R[-14]C[-1])"
    Range("H61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Range("H61").Offset(of, 0).Select
    Selection.Copy
    Range("J61").Offset(of, 0).Select
    Application.CutCopyMode = False
    Range("H61").Offset(of, 0).Select
    Selection.Copy
    Range("J61").Offset(of, 0).Select
    Range("J61").Offset(of, 0).Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C/R[-14]C)"
    Range("J61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("H61").Offset(of, 0).Select
    Selection.NumberFormat = "0.0%"
    Range("M61").Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C[-1]/R[-14]C[-1])"
    Range("M61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("O61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C/R[-14]C)"
    Range("O61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("R61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C[-1]/R[-14]C[-1])"
    Range("R61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("T61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C/R[-14]C)"
    Range("T61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("V61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C/R[-14]C)"
    Range("V61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("X61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C/R[-14]C)"
    Range("X61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("Z61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C/R[-14]C)"
    Range("Z61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("AB61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C/R[-14]C)"
    Range("AB61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("AE61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C[-1]/R[-14]C[-1])"
    Range("AE61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("AG61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C/R[-14]C)"
    Range("AG61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("AI61").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=1-(R[-1]C/R[-14]C)"
    Range("AI61").Offset(of, 0).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("H62").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C*12"
    Range("H62").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C="""","""",R[-1]C*12)"
    Range("H62").Offset(of, 0).Select
    Selection.AutoFill Destination:=Range("H62:AJ62").Offset(of, 0), Type:=xlFillDefault
    Range("H62:AJ62").Offset(of, 0).Select
    Selection.NumberFormat = "#,##0.0_);(#,##0.0)"
    Range("H84").Offset(of, 0).Select
    Range("H84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[-1]/(R[-37]C[-1]-R[-24]C[-1])"
    Range("H84").Offset(of, 0).Select
    Selection.Style = "Currency"
    Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
    Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    Range("J84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/(R[-37]C-R[-24]C)"
    Range("M84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[-1]/(R[-37]C[-1]-R[-24]C[-1])"
    Range("O84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/(R[-37]C-R[-24]C)"
    Range("R84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[-1]/(R[-37]C[-1]-R[-24]C[-1])"
    Range("T84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/(R[-37]C-R[-24]C)"
    Range("V84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/(R[-37]C-R[-24]C)"
    Range("X84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/(R[-37]C-R[-24]C)"
    Range("Z84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/(R[-37]C-R[-24]C)"
    Range("AB84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/(R[-37]C-R[-24]C)"
    Range("AE84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[-1]/(R[-37]C[-1]-R[-24]C[-1])"
    Range("AG84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/(R[-37]C-R[-24]C)"
    Range("AI84").Offset(of, 0).Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/(R[-37]C-R[-24]C)"
    Range("AJ84").Offset(of, 0).Select
    Range("H84:AJ84").Offset(of, 0).Select
    Selection.Style = "Currency"
    Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
    Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    Range("R84").Offset(of, 0).Select
    Range("G83:AJ83").Offset(of, 0).Select
    Selection.NumberFormat = "#,##0.0_);(#,##0.0)"
    Selection.NumberFormat = "#,##0_);(#,##0)"
    
    
 Next i
 
'format columns
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
Columns("L:L").EntireColumn.AutoFit
Columns("O:O").EntireColumn.AutoFit
Columns("Q:Q").EntireColumn.AutoFit
Columns("R:R").EntireColumn.AutoFit
Columns("T:T").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Columns("Z:Z").EntireColumn.AutoFit
Columns("AB:AB").EntireColumn.AutoFit
Columns("AD:AD").EntireColumn.AutoFit
Columns("AE:AE").EntireColumn.AutoFit
Columns("AG:AG").EntireColumn.AutoFit
Columns("AI:AI").EntireColumn.AutoFit


    'Enable screen updating
    Application.ScreenUpdating = True
End Sub

Open in new window

debug.jpg
0
philkar77
Asked:
philkar77
1 Solution
 
Saurabh Singh TeotiaCommented:
Change this line in your code...
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

to this only and this will work...
Selection.Insert Shift:=xlDown
 
Saurabh...
0
 
philkar77Author Commented:
Perfect! Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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