getting an error message in excel

November 7, 2010 at 11:53:01
Specs: Windows 7 64, core 2 duo t6660 2.2 ghz 2.2 ghz
Sub PRINT_AM_SCHEDULE()
'
' PRINT_AM_SCHEDULE Macro
' PRINT AM SCHEDULE
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Sheets("CALENDAR").Select
ActiveWindow.SmallScroll Down:=-20
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll Down:=-16
Range("A1:S196").Select
ActiveWindow.SmallScroll Down:=-212
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveSheet.PageSetup.PrintArea = "$A$1:$S$196"
Range("R7:R8,R26:R27").Select
Range("R26").Activate
ActiveWindow.SmallScroll Down:=20
Range("R7:R8,R26:R27,R47:R48").Select
Range("R47").Activate
ActiveWindow.SmallScroll Down:=20
Range("R7:R8,R26:R27,R47:R48,R67:R68").Select
Range("R67").Activate
ActiveWindow.SmallScroll Down:=16
Range("R7:R8,R26:R27,R47:R48,R67:R68,R80:R85").Select
Range("R80").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Range("R7:R8,R26:R27,R47:R48,R67:R68,R80:R85,S81").Select
Range("S81").Activate
ActiveWindow.SmallScroll Down:=16
Range("R7:R8,R26:R27,R47:R48,R67:R68,R80:R85,S81,R94:R95").Select
Range("R94").Activate
ActiveWindow.SmallScroll Down:=24
Range("R7:R8,R26:R27,R47:R48,R67:R68,R80:R85,S81,R94:R95,R113:R114").Select
Range("R113").Activate
ActiveWindow.SmallScroll Down:=16
Range( _
"R7:R8,R26:R27,R47:R48,R67:R68,R80:R85,S81,R94:R95,R113:R114,R133:R134,R136:R141,S137" _
).Select
Range("S137").Activate
ActiveWindow.SmallScroll Down:=24
Range( _
"R7:R8,R26:R27,R47:R48,R67:R68,R80:R85,S81,R94:R95,R113:R114,R133:R134,R136:R141,S137,R151:R152,R154:R159" _
).Select
Range("R154").Activate
ActiveWindow.SmallScroll Down:=20
Range( _
"R7:R8,R26:R27,R47:R48,R67:R68,R80:R85,S81,R94:R95,R113:R114,R133:R134,R136:R141,S137,R151:R152,R154:R159,R167:R168" _
).Select
Range("R167").Activate
ActiveWindow.SmallScroll Down:=12
Range( _
"R7:R8,R26:R27,R47:R48,R67:R68,R80:R85,S81,R94:R95,R113:R114,R133:R134,R136:R141,S137,R151:R152,R154:R159,R167:R168,R186:R187,R191:R196,S192" _
).Select
Range("S192").Activate
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=-8
Range("S186").Select
ActiveWindow.SmallScroll Down:=-76
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Range("R16:R17").Select
ActiveWindow.SmallScroll Down:=20
Range("R16:R17,R36:R37").Select
Range("R36").Activate
ActiveWindow.SmallScroll Down:=20
Range("R16:R17,R36:R37,R57:R58").Select
Range("R57").Activate
ActiveWindow.SmallScroll Down:=20
Range("R16:R17,R36:R37,R57:R58,R77:R78").Select
Range("R77").Activate
ActiveWindow.SmallScroll Down:=28
Range("R16:R17,R36:R37,R57:R58,R77:R78,R104:R106").Select
Range("R104").Activate
ActiveWindow.SmallScroll Down:=16
Range("R16:R17,R36:R37,R57:R58,R77:R78,R104:R106,R122:R123").Select
Range("R122").Activate
ActiveWindow.SmallScroll Down:=52
Range("R16:R17,R36:R37,R57:R58,R77:R78,R104:R106,R122:R123,R176:R177").Select
Range("R176").Activate
ActiveWindow.SmallScroll Down:=20
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Range("S175").Select
ActiveWindow.SmallScroll Down:=4
Sheets("PRINT PAGE").Select
ActiveSheet.Buttons.Add(145.5, 39.75, 98.25, 25.5).Select
Selection.OnAction = "PRINT"
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Button 1"
With Selection.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Button 1"
With Selection.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Characters.Text = "PRINT AM SCHEDULE"
With Selection.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.ShapeRange.ScaleWidth 1.47, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.38, msoFalse, msoScaleFromTopLeft
Range("E10").Select
End Sub

See More: getting an error message in excel

Report •

#1
November 7, 2010 at 12:52:00
Are you planning on asking a question or just posting a long piece of code with line after line of unnecessary instructions?

What's the purpose of all the ActiveWindow.ScrollRow = XX lines?

As far as I can tell they have no beneficial value.

Why don't you tell us what you are trying to do and what error you are getting.


Report •

#2
November 7, 2010 at 13:18:09
DerbyDad03

Here is the error iam getting

Run-time error '-21470224809 (80070057)':
the item with the specified name wasn't found.

I am trying to have excell print a schedule but excluded certain cell with in the print area

I selected the whole sheet as the print area and then highlighted the area i don't want to print and changed the font color to white or black depending on the cell color


Report •

#3
November 7, 2010 at 15:51:41
Before I would even consider trying to troubleshoot your code, you need to clean up all of the unnecessary instructions.

It's obvious that you recorded this code. While the VBA recorder is certainly a powerful tool, the fact that it records everything that you do results in bloated and inefficient code that is very hard to work with.

For example, the first 86 lines of code, from:

Sheets("CALENDAR").Select

to

ActiveSheet.PageSetup.PrintArea = "$A$1:$S$196"

do nothing but set the PrintArea to $A$1:$S$196.

That entire section of code could be reduced to 1 instruction:

Sheets("CALENDAR").PageSetup.PrintArea = "$A$1:$S$196" 

In the next 45 or so lines, you select ranges, then select them again and scroll around and activate cells and select other cells, repeating the selection of previous cells, etc. All that you really end up with is this:

 With Range("R7:R8,R26:R27,R47:R48,R67:R68,R80:R85,S81,R94:R95," & _
            "R113:R114,R133:R134,R136:R141,S137,R151:R152," & _
            "R154:R159,R167:R168,R186:R187,R191:R196,S192").Font
  .ThemeColor = xlThemeColorDark1
  .TintAndShade = 0
 End With

45 lines reduced to 4

After you use the VBA Recorder, it's your job to go into the VBA editor and clean up all the unnecessary code so that you end up with something useful and readable.

Try this:

Size the VBA editor window so that you can see your spreadsheet behind it.

Place your cursor anywhere in the code and press F8

This will allow you to single-step through your code and watch what happens as each line is executed. As you step through all of the scrolling and selecting, you'll see that nothing is really being accomplished.

Clean it all up, reduce it to those instructions that actually do something useful and then repost it.

Another tip:

When you post code in this forum, use the <pre> tags found above the Reply box. By placing your code between the pre tags, it will retain it's formatting (indents, etc.) and make it easier to read.


Report •
Related Solutions


Ask Question