Creating a total with VBA

Question

I'm currently trying to create a total, min, max, and average table at the bottom of the sheet. I would also like the "table" to start two cells below the last populated cell.

I am pulling in varying amounts of data which could be a single day, or as many as 100.

Sub max()

Dim N As Long

N = Cells(Rows.COUNT, "B").End(xlUp).Row

'Cells(N + 1, "B").Formula = "=MAX(B$13:B$44" & N & ")" <-COMMENTED OUT / THIS WORKS

Cells(N + 1, "B").Formula = "=IF(COUNT(B$13:B$44)=0,"",MAX(B$13:B$44))" & N & ")"

End Sub

This is what I have so far. I'm getting a 1004 error, and realize I am not calling the variable correctly. I will also need to do this across about 200 columns. Where am I going wrong?

Answer 1

EDIT: Update for non-contiguous tables.

This assumes you don't have anything below or to the right of the table on the worksheet and that your table starts at B13 (headers would be row 12):

Option Explicit

Public Sub BuildStatsTable()
    Dim lngMaxRow As Long
    Dim lngMaxCol As Long
    Dim lngCol As Long
    Dim strRng As String
    Dim rngLastUsed As Range

    Set rngLastUsed = GetLastRange(Cells(13, 2))
    lngMaxCol = rngLastUsed.Column
    lngMaxRow = rngLastUsed.Row

    For lngCol = 2 To lngMaxCol
        strRng = "R13C" & lngCol & ":R" & lngMaxRow & "C" & lngCol

        Cells(lngMaxRow + 2, lngCol).FormulaR1C1 = "=IF(COUNT(" & strRng & ")=0,"""",SUM(" & strRng & "))"
        Cells(lngMaxRow + 3, lngCol).FormulaR1C1 = "=IF(COUNT(" & strRng & ")=0,"""",MIN(" & strRng & "))"
        Cells(lngMaxRow + 4, lngCol).FormulaR1C1 = "=IF(COUNT(" & strRng & ")=0,"""",MAX(" & strRng & "))"
        Cells(lngMaxRow + 5, lngCol).FormulaR1C1 = "=IF(COUNT(" & strRng & ")=0,"""",AVERAGE(" & strRng & "))"
    Next lngCol
End Sub

Private Function GetLastRange(rngTopLeft As Range) As Range
    Dim rngUsed As Range
    Dim lngMaxRow As Long
    Dim lngMaxCol As Long

    Set rngUsed = Range(rngTopLeft, rngTopLeft.SpecialCells(xlCellTypeLastCell))

    lngMaxRow = rngUsed.Find(What:="*", _
                            After:=rngUsed.Cells(1), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    lngMaxCol = rngUsed.Find(What:="*", _
                            After:=rngUsed.Cells(1), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column

    Set GetLastRange = Cells(lngMaxRow, lngMaxCol)
End Function