How to use VBA variable in COUNTIF function?

[Terry]

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

 

[Don Guillett]

try

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

 

[zz]

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.here_goes_the_function

then you formula would be

activecell.value=application.worksheetfunction.countif(activesheet.usedrange,"abs")

 

[Terry]

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...

try

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

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

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

 

[Don Guillett]

Send it. My email is here

--
Don Guillett
SalesAid Software
(e-mail address removed)

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...

try

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

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

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

 

[Don Guillett]

or modify this to suit

Sub sumifvarcol()
mc = ActiveCell.Column - 1
'MsgBox Application.CountIf(Columns(mc), "abs")
MsgBox Application.CountIf(Range(Cells(7, mc), Cells(20, mc)), "abs")

End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

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...

try

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

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

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

 

[Terry]

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.

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.here_goes_the_function

then you formula would be

activecell.value=application.worksheetfunction.countif(activesheet.usedrange,"abs")


--
hope it works, regards!
---
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"")"

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

 

[Don Guillett]

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
(e-mail address removed)

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.

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.here_goes_the_function

then you formula would be

activecell.value=application.worksheetfunction.countif(activesheet.usedrange,"abs")


--
hope it works, regards!
---
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"")"

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

 

[Tushar Mehta]

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

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

 

[Terry]

Thank you so much! This second one works great!

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

Thousands of thanks again!

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

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

 

[Tushar Mehta]

You are welcome. Glad to be of help.

--
Regards,

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

Thank you so much! This second one works great!

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

Thousands of thanks again!

{snip}