Dennis,

No need to stay in R1C1 notation.

myRange.Formula = "=COUNTIF(" & myRange.Address & "," _
& Cells(myRange.Cells(1).Row, myOrigColumnIndex).Address(False, False) _
& ")"

Note, though, that the range myRange should NOT have formulas in it that
refer to itself. Circular references are usually a bad thing.

HTH,
Bernie
MS Excel MVP


"Dennis" wrote in message
...
XL 2003

Attempting to get the following cell formula into the appropriate VBA
syntax: =COUNTIF($J$41:$J$65,J41) so that a VBA process will properly
fill
in the formula in a range of cells (myRange).

Seems simple i.e. =COUNTIF(R41C10:R65C10,RC[-13])

The real issue is that I am attempting to have the range: R41C10:R65C10 to
be "myRange" as below:

Set myRange = Range(Cells(ActiveCell.Row, _
_ActiveCell.Column), Cells(ActiveSheet.UsedRange.Row _
- 1 + ActiveSheet.UsedRange.Rows.Count, _
ActiveCell.Column))

Also, I would like the RC[-13] above to be swapped to effectively with:
RC[myOrigColumnIndex-ActiveCell.Column])
this computes the column offset from my original starting point (column)

In short, one column wide and all the rows from ActiveCell to the end of
that column. (Both Columns and Rows are variable)

I just cannot get the VBA code correct correct. The failed code is:

myRange.FormulaR1C1 = "=COUNTIF(" & myRange &",RC[" & myOrigColumnIndex _
-ActiveCell.Column & "])"

Any help would be appreciated!

Dennis