Creating a Macro that compensates for new info

Taxacctintern

New Member
Joined
Jul 3, 2009
Messages
6
I have a file that weekly I download from an internal program and copy to an excel spreadsheet. The file tracks sales/items for sale. I then have to sort the items by 4 different criteria with an inserted space between the different sections. The first two sorts are done using these formulas (VLOOKUP(A3, 'Special price'!$A$2:$B$21, 2, FALSE)
VLOOKUP(A24, 'Special price'!$A$22:$B$91, 2, FALSE). The remaining two sorts are done using two of the columns. So what is included in the first sort of is anything from the current 19662 items that matches the first formula which I paste to all items then sort sm to large, then I add a spacer line, then run second for remaining items, sort sm to lg, then sort by the two columns. So, my question is, I can run the macro to perform how I need it to for the given weeks info, but say I download next week and there are 19672 items instead, the macro doesn't adjust to include those items. I have manually tried to add those lines in VBA but it usually ends up in error. Any ideas or do I just have to run sm macors to accomplish what I need to?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
The macro is this. If there is a better way to post this in here please let me know. Thanks. Oh BTW, all of the columns will remain the same and don't need any adjustment, just the # ranges are what may change week to week.
Sub FullPL41Update()
'
' FullPL41Update Macro
' Full Update
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Range("C1,D:D,E:E,F:F,G:G,H:H,I:I,J:J,L:L").Select
Range("L1").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("C1,D:D,E:E,F:F,G:G,H:H,I:I,J:J,L:L,N:N,O:O,Q:Q,S:S,T:T,U:U,V:V,W:W"). _
Select
Range("W1").Activate
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R3").Select
Sheets("Formula").Select
Range("C8").Select
Selection.Copy
Sheets("Price List 041").Select
ActiveSheet.Paste
Range("R3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-17], 'Special price'!R2C1:R21C2, 2, FALSE)"
Range("R3").Select
Selection.AutoFill Destination:=Range("R3:R19662")
Range("R3:R19662").Select
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"R3:R19662"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A3:R19662")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A19633").Select
Selection.End(xlUp).Select
Rows("22:22").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R23").Select
Sheets("Formula").Select
Range("C9").Select
Selection.Copy
Sheets("Price List 041").Select
ActiveSheet.Paste
Range("R23").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-17], 'Special price'!R22C1:R112C2, 2, FALSE)"
Range("R23").Select
Selection.AutoFill Destination:=Range("R23:R19663")
Range("R23:R19663").Select
Range("A23").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"R23:R19663"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A23:R19663")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A19636").Select
Selection.End(xlUp).Select
Range("A20").Select
ActiveWindow.SmallScroll Down:=51
Rows("104:104").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=9
Range("A106").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"P106:P19665"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A106:R19665")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-27
Range("A19609").Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=279
Range("P408").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
Rows("1095:1095").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1096").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"M1096:M19666"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A1096:R19666")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M19630").Select
Selection.End(xlUp).Select
Rows("1271:1271").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1272").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Price List 041").Sort.SortFields.Add Key:=Range( _
"K1272:K19667"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Price List 041").Sort
.SetRange Range("A1272:R19667")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("K19642").Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=-81
Range("K15016").Select
Selection.End(xlUp).Select
Rows("1296:1296").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=-3
End Sub
 
Upvote 0
This:

Range("R3:R19662")

can be rewritten as:

Range("R3:R" & Range("A" & Rows.Count).End(xlUp).Row)

so that 19662 becomes the row number of the last non-empty cell in column A.
 
Upvote 0
I will try that out. Thank you. I'll let you know if I have any issues. On the two fomulas, I am able to go into the macro and edit the range on those, so if next weeks download has 125 lines instead of 121 it will indclude the 4 new items?
 
Upvote 0
Sorry, the 125 was just an example #, the formula in the Macro is "=VLOOKUP(RC[-17], 'Special price'!R22C1:R112C2, 2, FALSE)"
so what I am wondering is for the section R112C2, if next weeks download that tab it is referencing goes to 125 instead of 112, can I just edit the macro and put R125C2 in its place?
 
Upvote 0
You can do this:

Code:
Dim LastRow as Long
With Worksheets("Special price")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-17], 'Special price'!R22C1:R" & LastRow & "C2, 2, FALSE)"
 
Upvote 0
Do I paste that immediately following ActiveCell.FormulaR1C1 = _ or below it, or just paste in place of where the formula is located?
 
Upvote 0
The code I posted replaces:

Code:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-17], 'Special price'!R22C1:R112C2, 2, FALSE)"
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top