Looping a Macro
I recorded a macro to calculate the ranking of the products on a monthly basis - I have monthly sales figures of those products for Jan - YTD I recorded a macro to sort the sales in desc order, and ranked them however i need to loop them for all months , which keeps on changing it could be Jan-Feb, Jan - May or whenever I need this report. Also the number of products (rows) could also change.
So i needed to record a macro that would do a dynamic vlookup.
Code i have so far is ranking for the month of Jan only -- i dont want to individually run the macro for each month , in short is it possible to loop the macro to run for all month.... also if new products are added , can vlookup include the new products as well? Pls advise.
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!RC[-1]:R[6]C[4],2)"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!R3C1:R9C6,2)"
Range("B3").Select
Selection.Copy
Range("B4:B9").Select
ActiveSheet.Paste
Range("B2").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B3"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C3").Select
ActiveCell.FormulaR1C1 = "1"
Range("C4").Select
ActiveCell.FormulaR1C1 = "2"
Range("C5").Select
ActiveCell.FormulaR1C1 = "3"
Range("C3:C5").Select
Selection.AutoFill Destination:=Range("C3:C9"), Type:=xlFillDefault
Range("C3:C9").Select
Sheets("Sheet3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!RC[-1]:R[6]C[1],3,0)"
开发者_运维知识库 Range("B3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R3C1:R9C3,3,0)"
Range("B3").Select
Selection.Copy
Range("B4:B9").Select
ActiveSheet.Paste
Pls let me know if any further information is required. TIA.
First, if you want to copy your formula in every cell of the B column
Range("B3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!R3C1:R9C6,2)"
lastRow = Cells(Application.Rows.Count, 2).End(xlUp).Row
Selection.AutoFill Destination:=Range("B:B" & lastRow)
can you explain the structure of your workbook/ table ?
精彩评论