countif syntax VBA

D

Dave

xl 2k
Win 2k

Trying to use VBA to enter a formula, must use RC
notiation so I can use variable number of rows, and then
will copy the formula using relative ranges and but still
mustuse a specific cell for the criteria.

sample snippet

Sub fixcount()
Dim counter As Integer
counter = 5
'what I want....
'Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R[4]C:R
[" & counter & "]C,"">" & value in a fixed cell & ")"""

'what I have --doesn't work run time 1004
Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R[4]C:R
[" & counter & "]C,"">" & "d1" & ")"""
End Sub

Is my problem a mix of RC and the "D1" notations?
TIA Dave
 
R

Rog

Just a few quotes in the wrong place Dave :

"=COUNTIF(R[4]C:R[" & counter & "]C,"">" & "d1"")"
 
T

Tom Ogilvy

You can't mix R1C1 notation and A1 notation in a single formula

ActiveCell.FormulaR1C1 = "=COUNTIF(R[4]C:R[" & counter & "]C,"">"" &R1C4)"

all on one line worked for me.

--
Regards,
Tom Ogilvy



Rog said:
Just a few quotes in the wrong place Dave :

"=COUNTIF(R[4]C:R[" & counter & "]C,"">" & "d1"")"
-----Original Message-----
xl 2k
Win 2k

Trying to use VBA to enter a formula, must use RC
notiation so I can use variable number of rows, and then
will copy the formula using relative ranges and but still
mustuse a specific cell for the criteria.

sample snippet

Sub fixcount()
Dim counter As Integer
counter = 5
'what I want....
'Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R[4] C:R
[" & counter & "]C,"">" & value in a fixed cell & ")"""

'what I have --doesn't work run time 1004
Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R[4]C:R
[" & counter & "]C,"">" & "d1" & ")"""
End Sub

Is my problem a mix of RC and the "D1" notations?
TIA Dave
.
Click to expand...
Click to expand...
 
G

Guest

thanks but not quite right yet

sample below
Sub Macro16()
Dim counter As Integer
counter = 20
ActiveCell.FormulaR1C1 = "=COUNTIF(R[4]C:R[" & counter
& "]C,"">" & "d1"")"
End Sub

enter value of 6 in d1
put dummy data in range d4 to 20
run the code in cell D3 and count is zero.

It seems to read D1 as a text values instead of the value
in D1

Dave
-----Original Message-----
Just a few quotes in the wrong place Dave :

"=COUNTIF(R[4]C:R[" & counter & "]C,"">" & "d1"")"
-----Original Message-----
xl 2k
Win 2k

Trying to use VBA to enter a formula, must use RC
notiation so I can use variable number of rows, and then
will copy the formula using relative ranges and but still
mustuse a specific cell for the criteria.

sample snippet

Sub fixcount()
Dim counter As Integer
counter = 5
'what I want....
'Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R[4] C:R
[" & counter & "]C,"">" & value in a fixed cell & ")"""

'what I have --doesn't work run time 1004
Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R[4] C:R
[" & counter & "]C,"">" & "d1" & ")"""
End Sub

Is my problem a mix of RC and the "D1" notations?
TIA Dave
.
Click to expand...
.
Click to expand...
 
T

Tom Ogilvy

See my post for a tested version that works.

--
Regards,
Tom Ogilvy

thanks but not quite right yet

sample below
Sub Macro16()
Dim counter As Integer
counter = 20
ActiveCell.FormulaR1C1 = "=COUNTIF(R[4]C:R[" & counter
& "]C,"">" & "d1"")"
End Sub

enter value of 6 in d1
put dummy data in range d4 to 20
run the code in cell D3 and count is zero.

It seems to read D1 as a text values instead of the value
in D1

Dave
-----Original Message-----
Just a few quotes in the wrong place Dave :

"=COUNTIF(R[4]C:R[" & counter & "]C,"">" & "d1"")"
-----Original Message-----
xl 2k
Win 2k

Trying to use VBA to enter a formula, must use RC
notiation so I can use variable number of rows, and then
will copy the formula using relative ranges and but still
mustuse a specific cell for the criteria.

sample snippet

Sub fixcount()
Dim counter As Integer
counter = 5
'what I want....
'Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R[4] C:R
[" & counter & "]C,"">" & value in a fixed cell & ")"""

'what I have --doesn't work run time 1004
Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R[4] C:R
[" & counter & "]C,"">" & "d1" & ")"""
End Sub

Is my problem a mix of RC and the "D1" notations?
TIA Dave
.
Click to expand...
.
Click to expand...
Click to expand...
 
G

Guest

Thanks Tom, I suspected that it was due to the mix in
notations.

Dave

-----Original Message-----
You can't mix R1C1 notation and A1 notation in a single formula

ActiveCell.FormulaR1C1 = "=COUNTIF(R[4]C:R[" & counter & "]C,"">"" &R1C4)"

all on one line worked for me.

--
Regards,
Tom Ogilvy



Just a few quotes in the wrong place Dave :

"=COUNTIF(R[4]C:R[" & counter & "]C,"">" & "d1"")"
-----Original Message-----
xl 2k
Win 2k

Trying to use VBA to enter a formula, must use RC
notiation so I can use variable number of rows, and then
will copy the formula using relative ranges and but still
mustuse a specific cell for the criteria.

sample snippet

Sub fixcount()
Dim counter As Integer
counter = 5
'what I want....
'Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R
Click to expand...
[4]
C:R
[" & counter & "]C,"">" & value in a fixed cell & ")"""

'what I have --doesn't work run time 1004
Sheets("Test").Range("d3").FormulaR1C1 = "=COUNTIF(R[4] C:R
[" & counter & "]C,"">" & "d1" & ")"""
End Sub

Is my problem a mix of RC and the "D1" notations?
TIA Dave
.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA for data validation in 3rd column does not run. I need help. 1
Macro autofills formula when a cell value is changed 4
display calculation next row 5
Problem with line of code / syntax 4
formula 5
Cleaner coding to repeat process 7
COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A" 1
Use of Variable in Sum 5

Top