VBA add formula to last column of table

[Sleeplol]

Hey Everyone,

My Table's columns are updated via userform and rows via query. I have the same unlocked formula that runs across the entire table and future columns and rows.

How would you auto add the formula to the next adjacent column when it is created?

Currently fumbling with the below. Would it be better to reference the table so it's not so static?

Sub ColumnFillFormula()
Dim last_Col As Long, last_Row As Long

last_Col = Cells(221, Columns.Count).End(xlToLeft).Column
last_Row = Cells(Rows.Count, last_Col).End(xlUp).Row
Range(Cells(221, last_Col + 1), Cells(last_Row, last_Col + 1)) = "=IFNA(INDEX(CVM!$B$2:$AZ$200,MATCH($AD221,CVM!$A$2:$A$200,0),MATCH(AE$220,CVM!$B$1:$AZ$1,0)),"")"
End Sub

 

[Peter_SSs]

See how this goes.

Sub ColumnFillFormula()
  Dim last_Col As Long, last_Row As Long
  
  last_Col = Cells(221, Columns.Count).End(xlToLeft).Column
  last_Row = Cells(Rows.Count, last_Col).End(xlUp).Row
  Range(Cells(221, last_Col + 1), Cells(last_Row, last_Col + 1)).Formula = "=IFNA(INDEX(CVM!$B$2:$AZ$200,MATCH($AD221,CVM!$A$2:$A$200,0),MATCH(AE$220,CVM!$B$1:$AZ$1,0)),"""")"
End Sub

BTW, is your table a formal Excel table (Insert -> Table)? If so, what is the table name (found in Formulas ribbon tab -> Name Manager)?

 

[Sleeplol]

Hi Peter,

Thanks for getting back to me.
Your script does exactly what I was asking, which means I wasn't properly asking for what I need.

Table: CVlogMatrix
Current range: AA220:AW315 (Rows will likely never increase, but Columns will increase as "Trainings" are added)

When a new training is added, the table expands by one column.
When this happens I need that column to be populated with the formula from the left (essentially dragging the formula over so that it then populates and applies to the new column)

The formula in my code is applies to the column in the table where the formula is first applied ("AE220").
Your script place that exact formula to column AX, however the formula for column AX needs to be:
=IFNA(INDEX(CVM!$B$2:$AZ$200,MATCH($AD221,CVM!$A$2:$A$200,0),MATCH(AX$220,CVM!$B$1:$AZ$1,0)),"")
Then AY would be:
=IFNA(INDEX(CVM!$B$2:$AZ$200,MATCH($AD221,CVM!$A$2:$A$200,0),MATCH(AY$220,CVM!$B$1:$AZ$1,0)),"")
... And so on, as columns are added.

My thoughts, but not necessarily correct: what if after a column is added, it calls a macro that deletes all table data from AE221: till the last region of the table, then pasted the formula to AE221 till last table column 221; then the table will automatically fill it rows??? (I don't know how to do this)

What do you think might be best??

 

[Peter_SSs]

OK, that's a bit clearer (I think). Try this one instead.

Sub ColumnFillFormula_v2()
  Dim last_Col As Long, last_Row As Long
  
  last_Col = Cells(221, Columns.Count).End(xlToLeft).Column
  last_Row = Cells(Rows.Count, last_Col).End(xlUp).Row
  Range(Cells(221, last_Col + 1), Cells(last_Row, last_Col + 1)).Formula = "=IFNA(INDEX(CVM!$B$2:$AZ$200,MATCH($AD221,CVM!$A$2:$A$200,0),MATCH(" & Cells(220, last_Col + 1).Address & ",CVM!$B$1:$AZ$1,0)),"""")"
End Sub

 

[Sleeplol]

Thanks Peter,
I believe your script is fighting with mine a bit.
My script adds a column to the table (that column now has a header but no formulas populating the rows)
You script then skips over the new column and adds the formula to the adjacent column (which is one column beyond the table)

Example:
"Original Table": Range AA200:AW315
Added new Training: Range AA220:AX315
Your script then jumps over column AX and populates column AY

 

[Peter_SSs]

I believe your script is fighting with mine a bit.

Could be, but we hadn't seen your code or known where this code would fit into it.

What about this then?

Sub ColumnFillFormula_v3()
  Dim last_Col As Long, last_Row As Long
  
  last_Col = Cells(220, Columns.Count).End(xlToLeft).Column
  last_Row = Cells(Rows.Count, last_Col).End(xlUp).Row
  Range(Cells(221, last_Col), Cells(last_Row, last_Col)).Formula = "=IFNA(INDEX(CVM!$B$2:$AZ$200,MATCH($AD221,CVM!$A$2:$A$200,0),MATCH(" & Cells(220, last_Col).Address & ",CVM!$B$1:$AZ$1,0)),"""")"
End Sub

 

[Peter_SSs]

Or if this is the only table on the sheet, you could try this shorter approach. (If not the only table it can be adjusted)

Sub ColumnFillFormula_v4()
  With ActiveSheet.ListObjects(1).DataBodyRange
      .Cells(1, .Columns.Count).Formula = "=IFNA(INDEX(CVM!$B$2:$AZ$200,MATCH($AD221,CVM!$A$2:$A$200,0),MATCH(" & .Cells(0, .Columns.Count).Address & ",CVM!$B$1:$AZ$1,0)),"""")"
  End With
End Sub

 

[Sleeplol]

YEAH!!!
That's the Huckleberry!
I really appreciate your patience with the unintentional information drip

Thanks Peter

 

[Peter_SSs]

You're welcome.

I forgot that you gave us the table name.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub ColumnFillFormula_v4()
  With ActiveSheet.ListObjects(1).DataBodyRange
  With Range("CVlogMatrix")
      .Cells(1, .Columns.Count).Formula = "=IFNA(INDEX(CVM!$B$2:$AZ$200,MATCH($AD221,CVM!$A$2:$A$200,0),MATCH(" & .Cells(0, .Columns.Count).Address & ",CVM!$B$1:$AZ$1,0)),"""")"
  End With
End Sub

 

[Sleeplol]

You're welcome.

I forgot that you gave us the table name.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub ColumnFillFormula_v4()
  With ActiveSheet.ListObjects(1).DataBodyRange
  With Range("CVlogMatrix")
      .Cells(1, .Columns.Count).Formula = "=IFNA(INDEX(CVM!$B$2:$AZ$200,MATCH($AD221,CVM!$A$2:$A$200,0),MATCH(" & .Cells(0, .Columns.Count).Address & ",CVM!$B$1:$AZ$1,0)),"""")"
  End With
End Sub

Yes, I updated it for all five tables on that sheet. It works perfectly