Concatenate Countif formula for VBA fill-in

G

Guest

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
 
B

Bernie Deitrick

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
 
G

Guest

Bernie,

Thanks for your time and knowledge.

It works, but all of the "filled in" formulas are exactly the same. This is
my goof in that I did not specify.

How do I get the formulas in to fill-in like:

=COUNTIF($J$41:$J$65,J41)
=COUNTIF($J$41:$J$65,J42)
=COUNTIF($J$41:$J$65,J43)
=COUNTIF($J$41:$J$65,J44)

it is currently filling in:

=COUNTIF($J$41:$J$65,J41)
=COUNTIF($J$41:$J$65,J41)
=COUNTIF($J$41:$J$65,J41)
=COUNTIF($J$41:$J$65,J41)

Dennis

********************************

Bernie Deitrick said:
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 said:
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
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Dennis,

That is exactly what I get:

=COUNTIF($J$41:$J$65,J41)
=COUNTIF($J$41:$J$65,J42)
=COUNTIF($J$41:$J$65,J43)
=COUNTIF($J$41:$J$65,J44)

Produced using

Dim myRange As Range
Dim myOrigColumnIndex As Integer

myOrigColumnIndex = 10
Set myRange = Range("$J$41:$J$65")
myRange.Formula = "=COUNTIF(" & myRange.Address & "," _
& Cells(myRange.Cells(1).Row, myOrigColumnIndex). _
Address(False, False) & ")"

HTH,
Bernie
MS Excel MVP


Dennis said:
Bernie,

Thanks for your time and knowledge.

It works, but all of the "filled in" formulas are exactly the same. This is
my goof in that I did not specify.

How do I get the formulas in to fill-in like:

=COUNTIF($J$41:$J$65,J41)
=COUNTIF($J$41:$J$65,J42)
=COUNTIF($J$41:$J$65,J43)
=COUNTIF($J$41:$J$65,J44)

it is currently filling in:

=COUNTIF($J$41:$J$65,J41)
=COUNTIF($J$41:$J$65,J41)
=COUNTIF($J$41:$J$65,J41)
=COUNTIF($J$41:$J$65,J41)

Dennis

********************************

Bernie Deitrick said:
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 said:
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
Click to expand...
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
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

Excel VBA 1
Vlookup in VBA (how to use a dynamic range) 2
High Lighting cells with conditional colour formats 1
Paste cell to last row in range 6
COUNTIF Cells in Range? 4
Excel VBA vlookup 1
Fill down only cells with Formulas VBA 1
Cells to be mandatory fill have a formula in it 1

Top