Inserting a column with formula through macro

Archimed

New Member
Joined
Apr 19, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Hello guys,

If I had to describe my situation, I have a dataset with changing number of rows and columns from which I further use this data with pivot table. The problem is that I have to construct a new variable (named "children") every time I change this dataset and now I have to do it manually.

Concretely, this variable is computed through this formula (the example is for the second row): =COUNTIF(BJ2:BS2; "<=10"). And I want a result from this formula for every row of my dataset starting the second row (in the first row there are the names of variables).

But as I don't want to do this every time the dataset changes, manually, I think about using a macro that would know how many rows and how many columns there are and where to put the variable and for how many rows it has to be counted.

If you guys had any recommendation, I would be grateful.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Herakles

Board Regular
Joined
Jul 5, 2020
Messages
73
Office Version
  1. 365
Platform
  1. Windows
The first cell in each column usually has one or a number of words in it which indicate what is in the column.
This value remains the same when the data below can change.

This first row is called a header row.

Is your first row a header row?

What do you mean when you use the word 'variable' as this is a word that has a special meaning in VBA, the Excel programming language.

Variables are used to store information to be referenced and manipulated in a computer program. ... It is helpful to think of variables as containers that hold information. Their sole purpose is to label and store data in memory. This data can then be used throughout the VBA program.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,976
Office Version
  1. 2010
Platform
  1. Windows
try this code:
VBA Code:
Sub test3()
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
Cells(i, lastcol + 1).Formula = "=COUNTIF(BJ" & i & ":BS" & i & ", " & Chr(34) & "<=10" & Chr(34) & ")"
Next i
I Forgot to say "Welcome to this forum"
 

Archimed

New Member
Joined
Apr 19, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
The first cell in each column usually has one or a number of words in it which indicate what is in the column.
This value remains the same when the data below can change.

This first row is called a header row.

Is your first row a header row?

What do you mean when you use the word 'variable' as this is a word that has a special meaning in VBA, the Excel programming language.

Variables are used to store information to be referenced and manipulated in a computer program. ... It is helpful to think of variables as containers that hold information. Their sole purpose is to label and store data in memory. This data can then be used throughout the VBA program.
Hello Herakles,

the way you describe it, the first row would be a header row, yes. And I use the word variables just because those data come from a questionare - so these are variables, but I don't assign them any special "variable status" in excel or VBA :)
 

Archimed

New Member
Joined
Apr 19, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

try this code:
VBA Code:
Sub test3()
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
Cells(i, lastcol + 1).Formula = "=COUNTIF(BJ" & i & ":BS" & i & ", " & Chr(34) & "<=10" & Chr(34) & ")"
Next i
I Forgot to say "Welcome to this forum"
Hi Offthelip, this code functions perfectly, thank you!
But as I'm a begginer at using excel macros, I have additional questions:

If I wanted this macro to run only when I click on a rectangle named "Button1" I can assign the macro to this button trought the dialog window after right-clicking the Button1, right?

But If I want this macro to run only for two sheets I have in the workbook, how do I tie the macro to only these two (named Export_A and Export_B)? Ideally I want to click a button on separate sheet and that would make the macro run for the Export_A and Export_B sheets. Is it possible?

And last thing - now the macro does the count for every row there is with data starting the second row, because in the first row, there are names of the variables... Is it also possible that the macro would also add text in the first row in the new counted column? So then the new counted column would be also named. Say the name would be "name", I can change it then.

Thank you very much!
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,976
Office Version
  1. 2010
Platform
  1. Windows
try this code. I have just duplicated the code forthe two sheets , if there were more than two is is probably worth doing in a loop but two sheets with very simple code duplicating is OK
VBA Code:
Sub test3()
With Worksheets("Export_A")
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
.Cells(i, lastcol + 1).Formula = "=COUNTIF(BJ" & i & ":BS" & i & ", " & Chr(34) & "<=10" & Chr(34) & ")"
Next i
.Cells(1, lastcol + 1) = "New Header A"
End With
With Worksheets("Export_B")
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
.Cells(i, lastcol + 1).Formula = "=COUNTIF(BJ" & i & ":BS" & i & ", " & Chr(34) & "<=10" & Chr(34) & ")"
Next i
.Cells(1, lastcol + 1) = "New Header B"
End With
End Sub
 
Solution

Archimed

New Member
Joined
Apr 19, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
try this code. I have just duplicated the code forthe two sheets , if there were more than two is is probably worth doing in a loop but two sheets with very simple code duplicating is OK
VBA Code:
Sub test3()
With Worksheets("Export_A")
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
.Cells(i, lastcol + 1).Formula = "=COUNTIF(BJ" & i & ":BS" & i & ", " & Chr(34) & "<=10" & Chr(34) & ")"
Next i
.Cells(1, lastcol + 1) = "New Header A"
End With
With Worksheets("Export_B")
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
.Cells(i, lastcol + 1).Formula = "=COUNTIF(BJ" & i & ":BS" & i & ", " & Chr(34) & "<=10" & Chr(34) & ")"
Next i
.Cells(1, lastcol + 1) = "New Header B"
End With
End Sub
Thank you very much man, this works perfectly!
 

Forum statistics

Threads
1,141,485
Messages
5,706,654
Members
421,459
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top