Solved

Excel Macro works in Office 2007 but not Office 2000

Posted on 2010-08-16
2
265 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2007 Macro to Change Column Formatting 3 37
List Box Entries Excel 2010 5 31
excel table with over 25000 rows. 3 33
Countdown Timer 2 17
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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