Thanks Don!

still not working. messagebox shows "0". Really frustrated for this
calculation. Spent 10 hours already.

any other ways to work it out? need to refer to columns left to activecell
and only at the same row because lower rows got repeating data.

mind to tell me your email? you may understand better if I pass you the
whole project. if you have time on it...

"Don Guillett" wrote:

try

Sub sumifvarcol()
mc = ActiveCell.Column - 1
MsgBox Application.CountIf(columns(mc), "abs")
End Sub

--
Don Guillett
SalesAid Software

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