Still not sure of what you are trying to do without seeing it but
Get RID of the selections.

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")

try
ran=activecell.Offset(0, 1 - C).Resize(1, C - 1)
ActiveCell.Offset(0, C -1)= Application.CountIf(Ran, "Abs")

--
Don Guillett
SalesAid Software

"Terry" wrote in message
...
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"?