VBA Copy Formula

[chaddres]

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". Any help is greatly appreciated.

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

 

[RoryA]

Since lastcol is a number, you can't use that syntax. I'd suggest:

Range(Cells(3, lastcol), Cells(Range("A" & Rows.Count).End(xlUp).Row, lastcol)).FillDown

 

[Peter_SSs]

What about?

With ActiveCell
  Cells(.Row, Columns.Count).End(xlToLeft).Offset(, 1).Resize(Range("A" & Rows.Count).End(xlUp).Row - .Row + 1).FormulaR1C1 = "=COUNT(RC2:RC[-1])"
End With

 

[chaddres]

Thank you so much. It did not work. Attaching the full code because I am very stuck Any help is appreciated. The attached picture shows my pivot table and the current formula (CD3). I need to copy the CD3 formula down for every row in the Pivot Table. The formula is a count of each row in the table from B to the last column of the table.

'Multiple Rate Check
    Sheets("Combined Time").Select
    Dim rng2 As Range
    Dim sht2 As Worksheet
    Dim pTable2 As PivotTable
    Set rng2 = ActiveSheet.Cells(1, 1).CurrentRegion
    Set sht2 = ActiveWorkbook.Worksheets.Add
    sht2.Name = "Rate Check Pivot"
    Set pTable2 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        rng2.Address, Version:=8).CreatePivotTable(TableDestination:= _
        sht2.Cells(1, 1), TableName:="PivotTable" & Format(Time, "hhmmss"))
    With pTable2
        With .PivotFields("SSN")
            .Orientation = xlRowField
            .Subtotals(1) = False
        End With
        With .PivotFields("Temp Rate")
            .Orientation = xlColumnField
            .Subtotals(1) = False
        End With
        .PivotFields("Reg Hours").Orientation = xlDataField
    End With
    Range("B3").Select
    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(Cells(3, lastcol), Cells(Range("A" & Rows.Count).End(xlUp).Row, lastcol)).FillDown

 

[RoryA]

Why are you putting a formula in lastcol + 1 column, but then trying to fill down the column to the left?

 

[chaddres]

So much closer!!!! The formula appeared in the column to the right of where I wanted it (not a big deal), but it is also including the "Grand Total" column. Any way around either of those? If not, I can make it work.

 

[chaddres]

Why are you putting a formula in lastcol + 1 column, but then trying to fill down the column to the left?

I am trying to just fill down below the formula .I am very stuck.

 

[RoryA]

Then you need to adjust that in the filldown line:

Range(Cells(3, lastcol + 1), Cells(Range("A" & Rows.Count).End(xlUp).Row - 1, lastcol + 1)).FillDown

 

[chaddres]

You both are amazing. Thank you and have a great day!

 

[RoryA]

Glad we could help.