this time it works:

ActiveCell.Offset(0, 1 - C).Resize(1, C - 1).Select
Set Ran = Selection
ActiveCell.Offset(0, C -1).Select
ActiveCell.Value = Application.WorksheetFunction.CountIf(Ran, "Abs")

The problem is: this macro is desifned to run at first time after people key
in the namelist. the whole project will start to format every worksheet
including charts later.
It is better to keep formular stay in the cell once all the worksheet
established since the macro runs really long time to comlete all formating.
And, I designed as erase all exsiting data and reinput when start up.

Any way to use the CUUNTIF formula when using VBA variable? if need to get
result through execution of macro, nobody will use this form already.

"zz" wrote:

are you using all of the cells with values?

then you can use the range "activecell.usedrange", it returns an object of
the type "Range" containing all cell with values in the current sheet, to
wich you can apply any formula or any method that applies to a normal range.

you can also avoid usin activecell.formular1c1 and use the value of the
formula itself

activecell.value=application.worksheetfunction.her e_goes_the_function

then you formula would be

activecell.value=application.worksheetfunction.cou ntif(activesheet.usedrange,"abs")


--
hope it works, regards!
---
zz [MX]
cuasi-musico,semi-poeta y loco

"Terry" wrote in message
...
I sincerely appreciate whoever's help on this question!

I need to use Excel formula functions of COUNTIF and SUM in VBA. The range
for calculation need to refer to a variable set in the VBA which is
changing
from one worksheet to another. However, the variable can't be recognized
by
the COUNTIF function. The actual statement is:

Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")"
or
Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")"

This formula is to count the number of "Abs" within the left (C-1) columns
of the activecell. "C" is the variable set in VBA that has to be used for
iteration steps in diferrent worksheet. Activecell is changing within 1
column of individual worksheet.
Must be writen into VBA because number of activecells is uncertain when
design.

How to make Excel function work by using the variable "C"?