how to use VBA variable in formula?

[Guest]

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"")"
"C" is the variable set in VBA that has to be used for iteration steps in
diferrent worksheet.
How to make Excel function work by using the variable "C"?

 

[zz]

i use a trick, i save the value of the variable in cell A6000 and name it
[the cell] the same as the variable, then reference this cell in my formula.

 

[Guest]

Thank you very much!
But the variable "C" is used to define one end of the range. if replaced by
another range name, the COUNTIF function still can't recognize it.

This function is aiming to count how many "ABC" within the left side (C-1)
columns of the activecell. "C" is changing in different worksheets and
activecell is is changing within one worksheet column. And, I must write it
within VBA instead of worksheet because of the number of activecell need to
get teh result is uncertain.

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

i use a trick, i save the value of the variable in cell A6000 and name it
[the cell] the same as the variable, then reference this cell in my formula.


--
---
zz [MX]
cuasi-musico,semi-poeta y loco

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"")"
"C" is the variable set in VBA that has to be used for iteration steps in
diferrent worksheet.
How to make Excel function work by using the variable "C"?

 

[Don Guillett]

Pls don't multipost. See ans in functions.

 

[Guest]

Ok sorry

Pls don't multipost. See ans in functions.
--
Don Guillett
SalesAid Software
(e-mail address removed)

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"")"
"C" is the variable set in VBA that has to be used for iteration steps in
diferrent worksheet.
How to make Excel function work by using the variable "C"?