Need to loop a VBA - Macros Code in Excel 2010?
Hello everyone
I need to apply a loop to the following code such that the constant (50) in ActiveCell.FormulaR1C1 = "=IF(C[-7]=50,C[-8],"""")" is replaced by a variable that has the values depending on column M as shown in the picture. Furthermore I also want my code to progress downwards and fill all the empty places, depending on the adjacent row value in column n. Do I need to switch over to absolute reference?
The code is as under:
Sub Percentile()
'
' Percentile Macro
'
'
ActiveCell.FormulaR1C1 = "Rough Work"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(C[-7]=50,C[-8],"""")"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L124252")
Range("L2:L124252").Select
Columns("L:L").Select
Selection.Copy
Columns("AG:AG").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Percentile 10%"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(C[16],0.1)"
Columns("R:U").Select
Selection.ClearContents
Range("R1").Select
ActiveCell.FormulaR1C1 = "Percentile 25%"
Range("S1").Select
ActiveCell.FormulaR1C1 = "Percentile 50%"
Range("T1").Select
ActiveCell.FormulaR1C1 = "Percentile 75%"
Range("U1").Select
ActiveCell.FormulaR1C1 = "Percentile 90%"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(C[16],0.1)"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(C[15],0.25)"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(C[14],0.5)"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(C[13],0.75)"
Range("U2").Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(C[12],0.9)"
Range("U3").Select
End Sub
You could find the Main Excel sheet here:
http://tinyurl.com/aqheuyp
Comments