fixing a run-time error'9': Subscript out of range

L

laksjd84

Joined
Jun 18, 2010
Messages
1
Reaction score
0
I tweaked my macro I use for daily shipping file modification at my company, but after I tweaked it, it gave me error msg. and I can't seem to find out whats wrong with it.

I highlighted a row below in orange.
when I tried to debug, that row automatically came out highlighted in yellow.
maybe whats where the problem lies.

thank you in advance!

Sub OS06172010()
'
' OS06172010 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Columns("A:A").Select
ActiveWorkbook.Worksheets("O83580_20100617013021").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("O83580_20100617013021").Sort.SortFields.Add Key:= _
Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("O83580_20100617013021").Sort
.SetRange Range("A2:Q51")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D300"), Type:=xlFillDefault
Range("D2:D300").Select
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Columns("B:C").Select
Selection.EntireColumn.Hidden = True
Columns("I:I").Select
Selection.NumberFormat = "00000"
Columns("J:J").Select
Selection.Replace What:="GRND", Replacement:="GND", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="3D", Replacement:="3DS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="2D", Replacement:="2DA", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="1D", Replacement:="1DA", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("L:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("M:M").ColumnWidth = 12.57
Selection.ColumnWidth = 12.14
Range("M1").Select
ActiveCell.FormulaR1C1 = "Weight"
Range("L1").Select
ActiveCell.FormulaR1C1 = "total weight"
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],[UPSTableRelationship.xlsx]Weight!R2C[-12]:R218C[2],15,0)"
Selection.AutoFill Destination:=Range("M2:M300"), Type:=xlFillDefault
Range("M2:M300").Select
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
Range("L2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]*RC[3])"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L51")
Range("L2:L51").Select
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Range("U8").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
Range("L51").Select
Selection.AutoFill Destination:=Range("L51:L300"), Type:=xlFillDefault
Range("L51:L300").Select
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Range("T2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]<199,99,RC[-6])"
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T300"), Type:=xlFillDefault
Range("T2:T300").Select
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 223
ActiveWindow.ScrollRow = 222
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Selection.Copy
Range("T2").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N2").Select
ActiveSheet.Paste
Range("A2:O51").Select
ActiveWindow.SmallScroll Down:=-54
Range("A2:O2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

variable number of rows. 2
Macro that deletes and pastes filterd rows and then some 2
I found these text strings printed out. What would they do if used in VBA? 2
Retaining data on clipboard after close without prompt. 1
macro to copy rows that contain a cell with a certain number 2
Macro Error 6
how to count/sum by function/macro to get the number of record to do copy/paste in macro 1
Macro works differently on different computers, same version of Ex 6

Top