Paste VBA formula in cell
 

I have a VBA macro which determines various ranges of cells that need to be
analyzed and assigns the ranges to different values, such as wrkRng. The
line below does partially what I need. It calculates correctly and puts the
value I'm looking for in the specified cell.

Range("B6").Select
ActiveCell.FormulaR1C1 = Evaluate("=COUNTIF(" & wrkRng.Address & ",""1"")")

The problem I have is the macro needs to be run I need to run the macro
before all of the cells have been updated. What I need is for the code to
put the actual formula in the cell so it is recalculated as the cells are
updated later and not the value from when it is initially run.

Since the ranges vary I can't just hard code the formula. But if wrkRng
happened to be D8:I25 at the time the macro was run, I want have this formula
entered into cell B6:

=COUNTIF($D$8:$I$25, "1")

Paste VBA formula in cell
 

Try this
ActiveCell.Formula = "=COUNTIF(" & wrkRng.Address & ",""1"")"


"PJ" wrote:

I have a VBA macro which determines various ranges of cells that need to be
analyzed and assigns the ranges to different values, such as wrkRng. The
line below does partially what I need. It calculates correctly and puts the
value I'm looking for in the specified cell.

Range("B6").Select
ActiveCell.FormulaR1C1 = Evaluate("=COUNTIF(" & wrkRng.Address & ",""1"")")

The problem I have is the macro needs to be run I need to run the macro
before all of the cells have been updated. What I need is for the code to
put the actual formula in the cell so it is recalculated as the cells are
updated later and not the value from when it is initially run.

Since the ranges vary I can't just hard code the formula. But if wrkRng
happened to be D8:I25 at the time the macro was run, I want have this formula
entered into cell B6:

=COUNTIF($D$8:$I$25, "1")

Paste VBA formula in cell
 

Why would you not post the entire code?