Seeking to insert formula via code, when cell <> "unused"

[auto.pilot]

Using XL2007: With a macro, I would like to carry down the formulas in Cells B3 and C3 only when the value in column A is not 'Unused'. If it helps, when a cell in column A = 'Unused', the sum of columns D:G in that row will also always be zero.

I've searched and also modified several codes, but this has me stumped.

Would appreciate any assistance.

thanks

jim

Excel Workbook
	A	B	C	D	E	F	G
2		Sum	Average	T1	T2	T3	T4
3	name1	1.739	0.43475	0.615	0.096	0.970	0.058
4	name2			0.866	0.379	0.580	0.974
5	name3			0.384	0.543	0.141	0.974
6	Unused			-	-	-	-
7	Unused			-	-	-	-
8	name4			1.771	0.775	1.186	1.992
9	Unused			-	-	-	-
Calculations

 

[Andrew Poulsom]

Try:

Sub Test()
    With Range("A2").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="<>Unused", Operator:=xlAnd
        With .Offset(1).Resize(.Rows.Count - 1)
            .Columns(2).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=SUM(RC[2]:RC[5])"
            .Columns(3).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=AVERAGE(RC[1]:RC[4])"
        End With
        .AutoFilter
    End With
End Sub

The code assumes that row 1 is empty.

 

[auto.pilot]

Works well on a small sample - now on to the big test.

thanks

jim

 

[auto.pilot]

Row 1 in the larger project is populated, so this is close to what I need. The headings in my filter area start in row 5, formulas in row 6. Column C is filter field 1. My formulas need to populate in several columns to the right, starting in column D. I have the R1C1 formulas figured out. All I need is a way to set the filter range to start in in C5.

Thanks

jim

 

[Andrew Poulsom]

Maybe try:

Sub Test()
    Dim LR As Long
    LR = Range("C" & Rows.Count).End(xlUp).Row
    With Range("C5:I" & LR)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="<>Unused", Operator:=xlAnd
        With .Offset(1).Resize(.Rows.Count - 1)
            .Columns(2).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=SUM(RC[2]:RC[5])"
            .Columns(3).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=AVERAGE(RC[1]:RC[4])"
        End With
        .AutoFilter
    End With
End Sub

 

[auto.pilot]

That's it!

Thank you - saved my day.

jim