VBA Help

[nikhil0311]

Book1
	A	B	C	D	E	F	G	H
1	Name	1-Dec	8-Dec	15-Dec	20-Dec	Average	Current week - Avg	%
2	Cognizant	$5	$4	$4	$9	$4	$5	108%
3	Credit Suisse	$6	$7	$8	$10	$7	$3	43%
4	Dominos	$3	$6	$7	$8	$5	$3	50%
Sheet1

Column A have Name
Date starts from Column B1 and is dynamic i.e. depending the no. of dates,
the range will differ from B1 to X
I want to calculate Average i.e. "=Average(B2:E2").
Average will always exclude latest week. i.e. current range of date is B to E
so we are excluding E. if the range is B to H we will exclude H.


I want to calculate current week minus average i.e. ("=e2-f2"). this should also change as per date range
I want to calculate % i.e. ("=iferror(e2/f2-1,"-")


bottom line if the date column increases, the formulas should auto adjust

below is my current code

Sub Macro1()
'
' Macro1 Macro
'


'
Range("F1").Select
ActiveCell.FormulaR1C1 = "Average"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Current week - Avg"
Range("H1").Select
ActiveCell.FormulaR1C1 = "%"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-2])"
Selection.AutoFill Destination:=Range("F2:F4")
Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Selection.AutoFill Destination:=Range("G2:G4")
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-3]/RC[-2]-1,""-"")"
Selection.AutoFill Destination:=Range("H2:H4")
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(RC[-3]/RC[-2]-1,""-"")"
Range("H3").Select
End Sub

 

[Fluff]

How about

Sub Macro1()
Dim e As Long
e = Range("A1").End(xlToRight).Column
Cells(1, e).Offset(, 1).Resize(, 3).Value = Array("Average", "Current week - Avg", "%")
With Range(Cells(2, e), Cells(4, e))
   .Offset(, 1).FormulaR1C1 = "=AVERAGE(RC2:RC" & e - 1 & ")"
   .Offset(, 2).FormulaR1C1 = "=RC" & e & "-RC" & e + 1 & ""
   .Offset(, 3).FormulaR1C1 = "=IFERROR(RC" & e & "/RC" & e + 1 & "-1,""-"")"
End With
End Sub

 

[nikhil0311]

This is perfect. Thank you sir, really appreciate it. 1 small change, can we have the % column in this format "0%"?

 

[Fluff]

add this as the last line

Columns(e + 3).Style = "Percent"