COUNT Formula VBA

[chaddres]

How do I use lastcol in my last line of code to count from B2 to lastcol2?

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "COUNT"
    Range("B2").Select
    Dim lastcol As Long
    lastcol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
    Cells(ActiveCell.Row, lastcol + 1).Select
    ActiveCell.FormulaR1C1 = "=COUNT(B2:??2)"

 

[smozgur]

Without changing anything in the current code:

ActiveCell.FormulaR1C1 = "=COUNT(RC[" & (1 - lastcol) & "]:RC[-1])"

With a couple of changes to avoid Select actions:

Dim lastcol As Long
    Range("B1").FormulaR1C1 = "COUNT"
    lastcol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
    Cells(2, lastcol + 1).FormulaR1C1 = "=COUNT(RC[" & (1 - lastcol) & "]:RC[-1])"

 

[chaddres]

Thank you again. I am stuck once again. I am trying to copy the formula (on line 4 of the code) down to the bottom of the range of data. The data starts in "lastcol3". My normal entry (for column B) would be "Range(B3:B & Range("A" & Rows.Count).End(xlUp).Row).FillDown" so I tried to use lastcol instead of the "B".

    Dim lastcol As Long
    lastcol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
    Cells(ActiveCell.Row, lastcol + 1).Select
    ActiveCell.Value = "=COUNT(RC[" & (1 - lastcol) & "]:RC[-1])"
    Range(lastcol & "3:" & lastcol & Range("A" & Rows.Count).End(xlUp).Row).FillDown

 

[smozgur]

Unfortunately, I don't want to but I have to make assumptions. Please understand if there is no sample data structure in the question, then the helper can only make assumptions.

If your data range starts from A1, and you want to add an additional column to this range that contains the formula, then please try the following. By the way, I suggest stopping using ActiveCell and Select methods. You do not have to Select a cell to change its properties. I recommend trying to write codes by reference as much as possible.

The following code:
1- Finds the entire range that is starting from A1 - CurrentRegion method.
2- Finds the cell on the adjacent column, and on the second row - Offset(1, rng.Columns.Count).
3- Resizes the cell to include all rows on the column along with the data range - Resize method.
4- Set the formula for all cells at once.

Dim rng As Range
    Set rng = Range("A1").CurrentRegion
    rng.Offset(1, rng.Columns.Count).Resize(rng.Rows.Count - 1, 1).FormulaR1C1 = "=COUNT(RC[" & (1 - rng.Columns.Count) & "]:RC[-1])"