How to put a function in a cell using VBA code?

[seragrefaat]

this is a macro recording for what i want to do

 Range("Table1[[#Headers],[Oct 4, 2020]]").Select
    Selection.ListObject.ListColumns.Add
    Range("Table1[[#Headers],[Oct 4, 2021]]").Select
    ActiveCell.FormulaR1C1 = "Absence nu"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=COUNTBLANK(Table1[@[Nov 4, 2020]:[Oct 4, 2020]])"
    Range("O3").Select
    Range("Table1[Absence nu]").FormulaR1C1 = _
        "=COUNTBLANK(Table1[@[Nov 4, 2020]:[Oct 4, 2020]])"
    ActiveWindow.SmallScroll Down:=-18

I want to replace the above with a vba code because headers are not fixed names also rows of the table are not a fixed number.

Thanks

 

[seragrefaat]

If you use Excel Max's code you would want to convert the sub declaration from

Sub CountBlankCells()

to...

Private Sub Workbook_Open()

You will want to place this under the "ThisWorkbook" object for the workbook you want this to run when it opens.

I still believe that you can do what you want to do without VBA though how are your dates determined? In the example you gave the dates are pretty random, does the class not take place every day? It looks like your old macro was adding columns so are you looking for something that adds a the current date to the table when someone opens the file?

No actually i have a piece of code that pop up msgbox with asking the user to enter number and I placed Excel Max Code under it. and it works perfectly.
This is the est method I mean VBA code for me because the original file is extracted from another application and dates are nor fixed wvery time also number of rows(students) are not fixed in each class.

 

[seragrefaat]

Sorry repeated reply

 

[EXCEL MAX]

I'm glad that you are satisfied.
For everyone who is interested for this problem I have similar code.
Now you can insert formula direct behind last column in the table for each row and save workbook without VBA code.
Formulas stays in the sheet.
Table works and you can use it with all calculation possbilities and inserting or removeing columns functionalities.

Sub CountBlankCellsFormula()

    Dim varNColumns, varNRows, varNLoop As Long
    Dim varWorksheetName As String
  
    varWorksheetName = "Sheet1"

    varNColumns = Sheets(varWorksheetName).Cells(1, Columns.Count).End(xlToLeft).Column
    varNRows = Sheets(varWorksheetName).Cells(Rows.Count, 1).End(xlUp).Row
  
    If Sheets(varWorksheetName).Cells(1, varNColumns).Value = "Absecnce" Then
            Sheets(varWorksheetName).Columns(varNColumns).Delete
            Sheets(varWorksheetName).Cells(1, varNColumns).Value = "Absecnce"
    Else
            Sheets(varWorksheetName).Cells(1, varNColumns + 1).Value = "Absecnce"
    End If
  
    varNColumns = Sheets(varWorksheetName).Cells(1, Columns.Count).End(xlToLeft).Column - 1
  
    Sheets(varWorksheetName).Cells(2, varNColumns + 1).FormulaR1C1 = _
        "=COUNTBLANK(RC[-" & varNColumns - 1 & "]:RC[-1])"
    Sheets(varWorksheetName).Cells(2, varNColumns + 1).AutoFill _
        Destination:=Range(Cells(2, varNColumns + 1), Cells(varNRows, varNColumns + 1))

End Sub