In some of my reports, after extraction, the number of columns changes every time based on the available information so I needed a way to put a formula in my extra column I created. For instance, the name of my extra column is “xxx” and one week, it can be in the column P, another week it can be in the column Z so this macro will find “xxx” no matter where is it then put the formula in the rows below.

When I use the macro ?

To find the column where the name is then fill up the rows of this column.

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Sub test()
Dim rFind As Range
Dim rRow As Long
Dim i
' change range where to search the word
With Range("A1:AZ1")
' change xxx by the word to search
Set rFind = .Find(What:="xxx", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
i = Split(rFind.Address, "$")(1)
End If
End With
' 1 = column A counting number of filled rows
rRow = Cells(Rows.Count, 1).End(xlUp).Row
' putting formula from row 2 = 2
Range(Cells(2, i), Cells(rRow, i)) = "=C2+E2"
End Sub

To put another formula to the next column, add those 3 lines:

' put below dim i
Dim j
' put below rRow
' + 1 to paste it to next column
j = Split(Cells(1, Range(i & 1).Column + 1).Address, "$")(1)
' put before end sub
Range(Cells(2, j), Cells(rRow, j)) = "=C2"