Thank you so much! This second one works great!

..Value or .FormulaR1C1 is not important. It's "&( )&" makes it work.

Thousands of thanks again!


"Tushar Mehta" wrote:

Untested suggestions:

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

Selection.Value = Evaluate("COUNTIF(RC["& (1-C) & "]:RC[-1], ""Abs"")"

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

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

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
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"?