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

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
.

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" wrote in 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
.

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
.

.

See my post for a tested version that works.

--
Regards,
Tom Ogilvy

wrote in message
...
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
.

.

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



"Rog" wrote in
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
.



.