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

 

[RustEE2020]

I have a few questions about this, I need a little more information.
What exactly are you trying to do here?
What is the point of placing "Absence nu" in a formula?
Can you show us a screenshot of the worksheet you are working with?
How do you want to activate this code?

 

[seragrefaat]

I have a few questions about this, I need a little more information.
What exactly are you trying to do here?
What is the point of placing "Absence nu" in a formula?
Can you show us a screenshot of the worksheet you are working with?
How do you want to activate this code?

Thanks for your response, i hope you able to help me.
I am trying to programe a code that calculate no. of absence of every student, the point of placing Absecnce nu in a formula is that beacuase my collegue will use the code in their class several times. I will activate the code by click a button in the ribbon.

Attendance Grades.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Students Names	Nov 4, 2020	Nov 3, 2020	Nov 1, 2020	Oct 28, 2020	Oct 27, 2020	Oct 21, 2020	Oct 19, 2020	Oct 18, 2020	Oct 14, 2020	Oct 13, 2020	Oct 11, 2020	Oct 6, 2020	Oct 4, 2020
2	A. D.			V		V	V	V		V		V	V
3	S. F	V		V		V	V		V		V	V		V
4	S. E	V	V	V		V	V		V	V	V	V	V
5	A. C.		V	V		V	V		V
6	W. E.	V	V	V	V	V	V	V	V	V	V	V	V	V
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:N6	Expression	=LEN(TRIM(B1))=0	text	NO
B1:N6	Cell Value	="V"	text	NO

Thanks

 

[RustEE2020]

So if I am reading this correctly you just need to count the abscence of "V"s by person?
But it also looks like you were doing something with adding a column when you were running this so is this run every day?
If you do not add a column every time the macro is run and instead prebuild the month you may be able to use just formulas.
You can use a formula like "=COUNTIF(C2:N2,"")" at the end of every row with a person's name and it will count up the number of absences.
If you build out the month entirely you can just add to that formula and subtract the days that have yet to pass in order to get an accurate count of the absences so far in the month.
This will save you a lot of time in adding a column every time and no VBA or macros are necessary.

Students Names

4-Nov-20

3-Nov-20

1-Nov-20

28-Oct-20

27-Oct-20

21-Oct-20

19-Oct-20

18-Oct-20

14-Oct-20

13-Oct-20

11-Oct-20

6-Oct-20

4-Oct-20

Total Absences

2

A. D.

V

V

V

V

V

V

V

5

3

S. F

V

V

V

V

V

V

V

5

4

S. E

V

V

V

V

V

V

V

V

V

V

2

5

A. C.

V

V

V

V

V

7

6

W. E.

V

V

V

V

V

V

V

V

V

V

V

V

0

 

[RustEE2020]

Also if you wanted to go super simple you could use this in a pivot table and pull that data out very easily as well.
You would even get extra functionality like being able to filter by month or date etc.

 

[seragrefaat]

So if I am reading this correctly you just need to count the abscence of "V"s by person?
But it also looks like you were doing something with adding a column when you were running this so is this run every day?
If you do not add a column every time the macro is run and instead prebuild the month you may be able to use just formulas.
You can use a formula like "=COUNTIF(C2:N2,"")" at the end of every row with a person's name and it will count up the number of absences.
If you build out the month entirely you can just add to that formula and subtract the days that have yet to pass in order to get an accurate count of the absences so far in the month.
This will save you a lot of time in adding a column every time and no VBA or macros are necessary.

Students Names

4-Nov-20

3-Nov-20

1-Nov-20

28-Oct-20

27-Oct-20

21-Oct-20

19-Oct-20

18-Oct-20

14-Oct-20

13-Oct-20

11-Oct-20

6-Oct-20

4-Oct-20

Total Absences

2

A. D.

V

V

V

V

V

V

V

5

3

S. F

V

V

V

V

V

V

V

5

4

S. E

V

V

V

V

V

V

V

V

V

V

2

5

A. C.

V

V

V

V

V

7

6

W. E.

V

V

V

V

V

V

V

V

V

V

V

V

I want to add this code in my excel so when i open a file (which contain the data in a table) i can perorm the macro on it and get my result.

 

[seragrefaat]

Also if you wanted to go super simple you could use this in a pivot table and pull that data out very easily as well.
You would even get extra functionality like being able to filter by month or date etc.

I have no expeirence in that, but i will try. Thanks a lot or your response

 

[EXCEL MAX]

Hello Serg...,
Insert this code in the module and run.

Sub CountBlankCells()

    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
    
    For varNLoop = 2 To varNRows
       Sheets(varWorksheetName).Cells(varNLoop, varNColumns) = _
       Application.WorksheetFunction.CountBlank(Sheets(varWorksheetName).Range(Cells(varNLoop, 1), Cells(varNLoop, varNColumns - 1)))
    Next varNLoop

End Sub

 

[RustEE2020]

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?

 

[seragrefaat]

Hello Serg...,
Insert this code in the module and run.

Sub CountBlankCells()

    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
  
    For varNLoop = 2 To varNRows
       Sheets(varWorksheetName).Cells(varNLoop, varNColumns) = _
       Application.WorksheetFunction.CountBlank(Sheets(varWorksheetName).Range(Cells(varNLoop, 1), Cells(varNLoop, varNColumns - 1)))
    Next varNLoop


[QUOTE="EXCEL MAX, post: 5589182, member: 468403"]
Hello Serg...,
Insert this code in the module and run.

[CODE=vba]
Sub CountBlankCells()

    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
  
    For varNLoop = 2 To varNRows
       Sheets(varWorksheetName).Cells(varNLoop, varNColumns) = _
       Application.WorksheetFunction.CountBlank(Sheets(varWorksheetName).Range(Cells(varNLoop, 1), Cells(varNLoop, varNColumns - 1)))
    Next varNLoop

End Sub

[/QUOTE]

Hello Serg...,
Insert this code in the module and run.

Sub CountBlankCells()

    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
   
    For varNLoop = 2 To varNRows
       Sheets(varWorksheetName).Cells(varNLoop, varNColumns) = _
       Application.WorksheetFunction.CountBlank(Sheets(varWorksheetName).Range(Cells(varNLoop, 1), Cells(varNLoop, varNColumns - 1)))
    Next varNLoop

End Sub

Thanks a lot you are very awesome, your work is very appreciated and was very helpful. I have another question,
what if i want to add another column named adjacent to the one named Absence and will be named y (Attendance Percent) Do I had to repeate the code againg? What wil be the contex of the function line? I mean: Application.WorksheetFunction. ?

And third coulmn named (Attendance Grades of 5) also.
Thanks again very much