Convert formula to value in a table column using VBA (2024)

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

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.

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 ?

gsinkinson said:

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

Click to expand...

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:

Akuini said:

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

VBA Code:

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

Click to expand...

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

Akuini said:

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

VBA Code:

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

Click to expand...

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

gsinkinson said:

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

Click to expand...

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 RangeWith 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").DataBodyRangeEnd 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 valueEnd WithEnd Sub

Example:
The result is in City_6

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.