Advertisement

05.09.2008 at 10:07AM PDT, ID: 23390155
[x]
Attachment Details

Excel Macro Range

Asked by RenitlahHelp in Microsoft Excel Spreadsheet Software

Tags: Microsoft, Excel, 2003

I have a macro that works with a range.  The range variable.  Since the macro has to run on worksheets with the same column names, but different number of records, how do I adjust my macro code to support this so the macro can run against any worksheet with any number of rows?Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:

Sub PAC_Codes_Macro_3()
'
' PAC_Codes_Macro_3 Macro
'
' Keyboard Shortcut: Ctrl+m
'
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Cells.Select
    Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("I2") _
        , Order2:=xlAscending, Key3:=Range("G2"), Order3:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, _
        DataOption3:=xlSortTextAsNumbers
    Rows("2:2").Select
    Rows("1:1").RowHeight = 26.25
    Columns("G:G").Select
    Selection.ColumnWidth = 7.43
    Columns("H:H").Select
    Selection.ColumnWidth = 7
    Columns("I:I").Select
    Selection.ColumnWidth = 8
    Columns("O:O").Select
    Selection.ColumnWidth = 7.14
    Columns("Q:Q").ColumnWidth = 10.14
    Columns("R:R").ColumnWidth = 4.71
    Columns("J:L").Select
    Selection.Insert Shift:=xlToRight
    Selection.ColumnWidth = 19
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],""-"",RC[-1])"
    Range("J2").Select
    Selection.Copy
    Range("J2:J494").Select
    ActiveSheet.Paste
    ActiveWindow.LargeScroll Down:=-1
    Range("J445").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J400").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J355").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J310").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J265").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J220").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J175").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J130").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J85").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J40").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("J2").Select
    Application.CutCopyMode = False
    Range("K2").Select
    ActiveWindow.SmallScroll Down:=-4
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "8300-8791"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "8300-8473"
    Range("K4").Select
    ActiveCell.FormulaR1C1 = "8300-8899"
    Range("K5").Select
    ActiveCell.FormulaR1C1 = "8300-8877"
    Range("K6").Select
    ActiveCell.FormulaR1C1 = "8300-8730"
    Range("K7").Select
    ActiveCell.FormulaR1C1 = "8300-8875"
    Range("K8").Select
    ActiveCell.FormulaR1C1 = "8600-8856"
    Range("K9").Select
    ActiveCell.FormulaR1C1 = "8600-8893"
    Range("K10").Select
    ActiveCell.FormulaR1C1 = "8600-8782"
    Range("K11").Select
    ActiveCell.FormulaR1C1 = "8400-8774"
    Range("K12").Select
    ActiveCell.FormulaR1C1 = "8500-8750"
    Range("K13").Select
    ActiveCell.FormulaR1C1 = "8300-8740"
    Range("K14").Select
    ActiveCell.FormulaR1C1 = "8300-8868"
    Range("K15").Select
    ActiveCell.FormulaR1C1 = "8300-8869"
    Range("K16").Select
    ActiveCell.FormulaR1C1 = "8500-8887"
    Range("K17").Select
    ActiveCell.FormulaR1C1 = "8400-8730"
    Range("K18").Select
    ActiveCell.FormulaR1C1 = "8400-8787"
    Range("K19").Select
    ActiveCell.FormulaR1C1 = "8400-8403"
    Range("K20").Select
    ActiveCell.FormulaR1C1 = "8400-8772"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Invoice Codes"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "PAD Codes"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Mis Coded Invoices"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=INDIRECT(""L""&ROW())=""No Match"""
    Range("J3").Select
    Application.CommandBars("Circular Reference").Visible = False
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISNA(MATCH(RC[-2],C[-1],0)),""no match"","""")"
    Range("L2").Select
    Selection.Copy
    Range("L2:J494").Select
    ActiveSheet.Paste
    ActiveWindow.LargeScroll Down:=-1
    Range("L430").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L385").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L340").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L295").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L250").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L205").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L160").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L115").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L70").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L25").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(ISNA(MATCH(RC[-2],C[-1],0)),""No Match"","""")"
    Range("L2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    ActiveWindow.LargeScroll Down:=-1
    Range("L405").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L360").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L315").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L270").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L225").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L180").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L135").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L90").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L45").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(ISNA(MATCH(RC[-2],C[-1],0)),""Match"","""")"
    Range("L2").Select
    Selection.Copy
    Range("L2:L20").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=INDIRECT(""l""&ROW())=""no match"""
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions(1).Interior.ColorIndex = 6
    ActiveWindow.SmallScroll Down:=1
    ActiveWindow.LargeScroll Down:=-1
    Range("L406").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L361").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L316").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L271").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L226").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L181").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L136").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L91").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("L46").Select
    ActiveWindow.LargeScroll Down:=-1
    Cells.Select
    Selection.Sort Key1:=Range("L2"), Order1:=xlAscending, Key2:=Range("J2") _
        , Order2:=xlAscending, Key3:=Range("O2"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortTextAsNumbers
    ActiveWindow.LargeScroll Down:=10
    Range("A494").Select
End Sub
[+][-]05.09.2008 at 12:44PM PDT, ID: 21535890

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Microsoft Excel Spreadsheet Software
Tags: Microsoft, Excel, 2003
Sign Up Now!
Solution Provided By: seriousnick
Participating Experts: 1
Solution Grade: A
 
 
[+][-]05.13.2008 at 07:50AM PDT, ID: 21555612

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628