Reference relative to activecell
 

I'm working with Excel 2003 and need to populate a worksheet
programmatically. I can't figure out the correct syntax and need some
help.

I need to add a row count at the end of each row but won't know how
many rows and columns have been filled. I can get the ActiveCell to
the rightmost column and get the last row without much trouble but
that's about as far as I am getting. The .Row property returns the
row number of the last row but .Column returns a number not a letter.
The .Address property returns the correct information but includes the
column and row together. Breaking that string apart to get the column
seems awkward. (It could be $F$7 or $AT$199)

Once I've selected the correct column and row what syntax will allow
me to add a formula to that column? I'm trying to work with something
like:

Range(CurrentColumn3:CurrentColumnLastRow)
.Formula = "=COUNTIF(C3:CurrentColumn3, "X")

I can't figure out the syntax and would appreciate help.

Reference relative to activecell
 

Not quite sure of what you want but try this idea

Sub dd()
cc3 = ActiveCell.Column
cc3lr = Cells(Rows.Count, cc3).End(xlUp).Row
Cells(cc3lr + 1, cc3).Formula = _
"=COUNTIF(C3:c" & cc3lr & ", ""X"")"
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Arch" wrote in message
...
I'm working with Excel 2003 and need to populate a worksheet
programmatically. I can't figure out the correct syntax and need some
help.

I need to add a row count at the end of each row but won't know how
many rows and columns have been filled. I can get the ActiveCell to
the rightmost column and get the last row without much trouble but
that's about as far as I am getting. The .Row property returns the
row number of the last row but .Column returns a number not a letter.
The .Address property returns the correct information but includes the
column and row together. Breaking that string apart to get the column
seems awkward. (It could be $F$7 or $AT$199)

Once I've selected the correct column and row what syntax will allow
me to add a formula to that column? I'm trying to work with something
like:

Range(CurrentColumn3:CurrentColumnLastRow)
.Formula = "=COUNTIF(C3:CurrentColumn3, "X")

I can't figure out the syntax and would appreciate help.

Reference relative to activecell
 

Rather than worrying about selecting the right cell you should start with
the starting point which is I gather C3.

Sub a()
Dim RowCount As Integer
Dim ColCount As Integer
With Range("C3")
RowCount = Range(.Cells(1), .End(xlDown)).Rows.Count
ColCount = Range(.Cells(1), .End(xlToRight)).Columns.Count
.Offset(0, ColCount).Resize(RowCount).FormulaR1C1 = _
"=countif(RC[-" & ColCount & "]:RC[-1],""x"")"
End With
End Sub

You can replace the C3 with any cell and it should work fine.

--
Jim
"Arch" wrote in message
...
I'm working with Excel 2003 and need to populate a worksheet
programmatically. I can't figure out the correct syntax and need some
help.

I need to add a row count at the end of each row but won't know how
many rows and columns have been filled. I can get the ActiveCell to
the rightmost column and get the last row without much trouble but
that's about as far as I am getting. The .Row property returns the
row number of the last row but .Column returns a number not a letter.
The .Address property returns the correct information but includes the
column and row together. Breaking that string apart to get the column
seems awkward. (It could be $F$7 or $AT$199)

Once I've selected the correct column and row what syntax will allow
me to add a formula to that column? I'm trying to work with something
like:

Range(CurrentColumn3:CurrentColumnLastRow)
.Formula = "=COUNTIF(C3:CurrentColumn3, "X")

I can't figure out the syntax and would appreciate help.