philkar77
asked on
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
debug.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER