Hi,

I have a control chart macro which is being used for patient doses of a medication. Right now my macro automatically fills columns D-N down to row 100. However, I would like it to stop at the last row of data entered in column A. Any assistance will be greatly appreciated. I have attached the code related to the formulae a

J

Sub Control_Chart_1()
'
' Control_Chart_1 Macro
'

'
Range("D1").Select
ActiveCell.FormulaR1C1 = "Target"
Range("E1").Select
ActiveCell.FormulaR1C1 = "UCL"
Range("F1").Select
ActiveCell.FormulaR1C1 = "LCL"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Rule 1"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Rule 2"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Rule 1 AND 2"
Range("D2").Select
ActiveCell.FormulaR1C1 = "2"
Range("E2").Select
ActiveCell.FormulaR1C1 = "3"
Range("F2").Select
ActiveCell.FormulaR1C1 = "1"
Range("D2:F2").Select
Selection.AutoFill Destination:=Range("D2: F100"), Type:=xlFillDefault
Range("D2:F100").Select
ActiveWindow.SmallScroll Down:=-141
Range("G1").Select
ActiveCell.FormulaR1C1 = "Category"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-5]-RC[-3]"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G100"), Type:=xlFillDefault
Range("G2:G100").Select
Range("H3").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[-1]>1, RC[-1]<-1, AND(R[-1]C[-1]<0,RC[-1]<0), AND(R[-1]C[-1]>0,RC[-1]>0)),1,0)"
Range("H3").Select
Selection.AutoFill Destination:=Range("H3:H100"), Type:=xlFillDefault
Range("H3:H100").Select
Range("I5").Select
ActiveCell.FormulaR1C1 = "=IF(STDEV(R[-3]C[-7]:RC[-7])>1,1,0)"
Range("I5").Select
Selection.AutoFill Destination:=Range("I5:I100"), Type:=xlFillDefault
Range("I5:I100").Select
Range("J3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]*2+RC[-1]"
Range("J3").Select
Selection.AutoFill Destination:=Range("J3:J100"), Type:=xlFillDefault
Range("J3:J100").Select
Range("K1").Select
ActiveCell.FormulaR1C1 = "Rule 3"
Range("K9").Select
ActiveCell.FormulaR1C1 = _
"=5*IF(AVERAGE(R[-3]C[-9]:RC[-9])>2*AVERAGE(R[-7]C[-9]:R[-4]C[-9]),1,0)"
Range("K9").Select
Selection.AutoFill Destination:=Range("K9:K100"), Type:=xlFillDefault
Range("K9:K100").Select
Range("L1").Select
ActiveCell.FormulaR1C1 = "Rules 1, 2, 3"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L100"), Type:=xlFillDefault
Range("L2:L100").Select
Range("M1").Select
ActiveCell.FormulaR1C1 = "Technique Error Means"
Range("M5").Select
ActiveCell.FormulaR1C1 = "=IF(AVERAGE(R[-3]C[-10]:RC[-10])<-0.25,1,0)"
Range("M5").Select
Selection.AutoFill Destination:=Range("M5:M100"), Type:=xlFillDefault
Range("M5:M100").Select
Range("N1").Select
ActiveCell.FormulaR1C1 = "Technique Error Rules"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(OR(RC[-11]<-1,RC[-1]=1),1,0)"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N100"), Type:=xlFillDefault
Range("N2:N100").Select