Solved

Excel Macro works in Office 2007 but not Office 2000

Posted on 2010-08-16
2
263 Views
Last Modified: 2012-06-21
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
Comment
Question by:philkar77
2 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 500 total points
ID: 33449372
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
 

Author Closing Comment

by:philkar77
ID: 33450659
Perfect! Thanks!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA User Form Help 21 29
Dropbox in Windows Server 2008 4 32
Excel Question 17 15
Need excel formula correction. 5 14
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

832 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