Sum Relative Number of Cells in Column
 

Hi, the code below comes filters a column to return all values equal to or
above 0.001.

Then the code is supposed to take a number in cell B464 and subtract the sum
of all values in the range above (which should be equal to or above 0.001)
but I can't get it to work, it always sums the wrong cells...

Any help? Thanks very much.


-- the broken part:

(note: B463 is between the Total Value in B464, and all the other values in
B7:B462 -- but about half of these values have already been filtered out.
Basically the point is I'm trying to get balance of all values less than
0.001 with this formula. So if the total is 500, and the values above .001 =
400, then the balance is 100.)

Range("B463").Select
ActiveCell.FormulaR1C1 = "=R[1]C-SUM(R[-308]C:R[-6]C)"


The entire code:

Range("C6").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=.001", Operator:=xlAnd
Range("A6").Select
Selection.End(xlDown).Select
Range("A463").Select
ActiveCell.FormulaR1C1 = "Other"
Range("B463").Select
ActiveCell.FormulaR1C1 = "=R[1]C-SUM(R[-308]C:R[-6]C)"
Range("C463").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R[1]C[-1]"
Range("D463").Select
ActiveCell.FormulaR1C1 = "=1-R[-154]C"

Sum Relative Number of Cells in Column
 

Use SUBTOTAL() rather than SUM(). SUBTOTAL() can be made to ignore data that
has been filtered out. First try it in the worksheet and then move to VBA.



--
Gary's Student


"SteveC" wrote:

Hi, the code below comes filters a column to return all values equal to or
above 0.001.

Then the code is supposed to take a number in cell B464 and subtract the sum
of all values in the range above (which should be equal to or above 0.001)
but I can't get it to work, it always sums the wrong cells...

Any help? Thanks very much.


-- the broken part:

(note: B463 is between the Total Value in B464, and all the other values in
B7:B462 -- but about half of these values have already been filtered out.
Basically the point is I'm trying to get balance of all values less than
0.001 with this formula. So if the total is 500, and the values above .001 =
400, then the balance is 100.)

Range("B463").Select
ActiveCell.FormulaR1C1 = "=R[1]C-SUM(R[-308]C:R[-6]C)"


The entire code:

Range("C6").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=.001", Operator:=xlAnd
Range("A6").Select
Selection.End(xlDown).Select
Range("A463").Select
ActiveCell.FormulaR1C1 = "Other"
Range("B463").Select
ActiveCell.FormulaR1C1 = "=R[1]C-SUM(R[-308]C:R[-6]C)"
Range("C463").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/R[1]C[-1]"
Range("D463").Select
ActiveCell.FormulaR1C1 = "=1-R[-154]C"

Sum Relative Number of Cells in Column
 

Thank you. Just what I needed.