Convert formula to value in a table column using VBA

[gsinkinson]

Been away from Excel for a while and now trying to get re-started ...

I have a worksheet table with numbers that I want to add a column to,
perform a calculation for each row in the table and then convert the cell contents to values.
I will add more columns to the sheet for other calculations.

I can add a new column with this Sub:

I know I can add a name parameter, but I plan to use this Sub repeatedly

It generates the following:

I want to create a Sub for each different calculation that does the following:
1.renames the header of the new column - for example, 5th to Rng
2.adds a calculation to the first row of the new column - for example, 4th number minus 1st number
(the table will then replicate that calculation for all the cells in the new column)
3.changes the formula to a value in the new cells

Hopefully ending up with something like:

Here are my 3 questions relating to VBA and tables:

A. I'm not sure how to rename the table column header from 5th to Rng

B. I'm not sure how move down 1 row to the first data row and
add ActiveCell.Formula = "=D2-A2", but using column header names 4th and 1st.

C. I'm also not sure how to convert the cell formulas to values.

I hope to create a Sub for each one of the calculations, something like:

I used to know how to do this but I've lost my notes and examples.
I think the 95 degree room I'm in has fried my brains ...

I realize that the add and convert can be their own subs
and be called. I'm open to suggestions

 

[myall_blues]

The macro recorder is your friend! I find table terminology totally baffling.

I reproduced your table and recorded steps. To rename the new column it recorded:

    Range("Table1[[#Headers],[5th]]").Select
    ActiveCell.FormulaR1C1 = "Rng"

which can be changed to

    Range("Table1[[#Headers],[5th]]").Value = "Rng"

Similarly, adding the formula produced

    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=[@1st]+[@4th]"

which can be changed to

    Range("E2").FormulaR1C1 = "=[@1st]+[@4th]"

and so on.

 

[gsinkinson]

Your rename code worked.
Range("Table1[[#Headers],[5th]]").Value = "Rng"
but am having trouble with adding a formula.

Since I will be adding multiple columns later,
the range won't always be Range("E2").

I tried using
Range("Table1[[#Data],[Rng]]").Value = "=[4th]-[1st]"
but I get Run-time error '1004'
Application-defined or object-defined error.

Is there a simple way to avoid referencing cell E2 specifically
to move down 1 row from the Header column Rng ?

 

[Akuini]

Since I will be adding multiple columns later,
the range won't always be Range("E2").

Do you mean you want to refer to the last column of the table?
Maybe something like this:

With ActiveSheet.ListObjects("Table1").DataBodyRange
    .Cells(1, .Columns.Count).Formula = "=" & .Cells(1, 4).Address(0, 0) & "-" & .Cells(1, 1).Address(0, 0)
End With

Example:

Book2
	A	B	C	D	E	F
1	q1	q2	q3	q4	q5	q6
2	1	2	3	4	5	3
3	1	2	3	4	5
4	1	2	3	4	5
Sheet3

Cell Formulas
Range	Formula
F2	F2	=D2-A2

 

[gsinkinson]

Do you mean you want to refer to the last column of the table?
Maybe something like this:

With ActiveSheet.ListObjects("Table1").DataBodyRange
    .Cells(1, .Columns.Count).Formula = "=" & .Cells(1, 4).Address(0, 0) & "-" & .Cells(1, 1).Address(0, 0)
End With

Example:

Book2
	A	B	C	D	E	F
1	q1	q2	q3	q4	q5	q6
2	1	2	3	4	5	3
3	1	2	3	4	5
4	1	2	3	4	5
Sheet3

Cell Formulas
Range	Formula
F2	F2	=D2-A2

Perfect ! Exactly what I needed.
One last question ...
How would you convert the cells in the last column from a formula to a value?

 

[gsinkinson]

Do you mean you want to refer to the last column of the table?
Maybe something like this:

With ActiveSheet.ListObjects("Table1").DataBodyRange
    .Cells(1, .Columns.Count).Formula = "=" & .Cells(1, 4).Address(0, 0) & "-" & .Cells(1, 1).Address(0, 0)
End With

Example:

Book2
	A	B	C	D	E	F
1	q1	q2	q3	q4	q5	q6
2	1	2	3	4	5	3
3	1	2	3	4	5
4	1	2	3	4	5
Sheet3

Cell Formulas
Range	Formula
F2	F2	=D2-A2

That's great. Is there a way to do that using structured references like "=q4-q1" ?

 

[Akuini]

That's great. Is there a way to do that using structured references like "=q4-q1" ?

Do you mean you want to refer to a column by its header?
My understanding is you're looking to use a formula on the last column where the formula makes reference to specific columns based on their headers and then convert the formula to value.
Here's an option:

Sub gsinkinson_1()
Dim a As Range, b As Range, c As Range
With ActiveSheet.ListObjects("Table1")
    Set a = .ListColumns(.Range.Columns.Count).DataBodyRange 'get the last column DataBodyRange, i.e  City_6
    Set b = .ListColumns("City_4").DataBodyRange             'get City_4 DataBodyRange
    Set c = .ListColumns("City_1").DataBodyRange
End With
    
With a
    .Cells(1).Formula = "=" & b.Cells(1).Address(0, 0) & "-" & c.Cells(1).Address(0, 0)  'fill the formula in first cell in a
    .FillDown                                  'apply formula to the whole column
    .Value = .Value                            'convert formula to value
End With
End Sub

Example:
The result is in City_6

Book1
	A	B	C	D	E	F
1	City_1	City_2	City_3	City_4	City_5	City_6
2	1	2	3	4	5	3
3	2	2	3	4	5	2
4	3	2	3	4	5	1
Sheet1

 

[gsinkinson]

I wanted to thank you for your help.
After trying the suggestions, I decided to create a sub for each new column and calculation.
This is how I handled the first one:

It turns out you don't have to replicate a formula into every cell of the column.
Placing the formula in any one cell of the table column will add it to every cell in the column.
I chose to put it in the 2nd row of the table.

That approach worked many additional columns, but seems to hang up with multi-parameter functions.
If I apply a formula manually as shown below, it works properly:

But when I try to do it in VBA, I get a syntax error:

Using structured references with Excel tables - Microsoft Support indicates escape characters are needed in formulas with [ ] # ' @.
It also suggests spaces for readability.


I tried using spaces : ActiveCell.Formula = "=COUNTIF(Table1[ @[Jan]:[Mar] ], "<50")" but it fails with the same error.

I tried : ActiveCell.Formula = "=COUNTIF(Table1'[ '@'[Jan']:'[Mar'] '], "<50")" but it fails with the same error.

Can you figure out where the error is?

If I can resolve this, I'll probably try to create a sub and pass it the name, formula, and format parameters.
Once I'm done adding all the columns, I convert the entire table's calculations to values.