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