| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Concatenate Countif formula for VBA fill-in
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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" wrote: 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 |
|
#4
|
|||
|
|||
|
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" wrote in message ... 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Creating an EXCEL COUNTIF formula for a range of values | Excel Discussion (Misc queries) | |||
| Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
| Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
| Countif Formula with 2 calculations | Excel Discussion (Misc queries) | |||
Linear Mode
