VBA: how to replace formula with value

[schubidu]

Hi,

I have an issue with a VBA code.

I have a table with a column which contains the formula.

In the next step I need to replace the formula with the value.

The code so far is:

Sub Zeilennummer_2()
'
' Makro1 Makro
'

'
Selection.ShowDetail = True
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=ROW()-1"
Columns("B:B").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Rechnungsnummer"
End Sub

How can I replace "=ROW()-1" with the value?

Thanks

Alexander

[KOKOSEK]

Please Login or Register  to view this content.

[Kenneth Hobson]

One seldom needs: Select, Selection, Activate, ActiveCell. Just use the range directly.

Why add a formula, you could just evaluate the formula. e.g.

Please Login or Register  to view this content.

[jolivanes]

Can you explain the logic of this?
If you select a cell in row 2 (Range("B2").Select) and use the formula as you have it, the result will always be 1.
Why use a formula? You can just use

Please Login or Register  to view this content.

[schubidu]

OK, I think I need to explain a bit here.
In general, I'm pretty profund in excel but not in VBA coding.
What I try to accomplish is that from a pivot I just want one click to open the details AND it should add a row at the beginning of the details table, counting all rows. The number of rows can vary from 1 to several hundred. So my idea was to add a macro that simply does this and it worked. However, in the last step I need to replace the formular with the value. However, I cannot select the whole column and past value. I get an error back (because the table is formatted as an excel table).

When I use the formular, it completes all rows from the first to the last row, no matter whow many rows I have.

I used the macro recorder but I cannot replicate the last step of the macro on other sheets because it references to "sheet x".

It's a very simply task but with no coding knowledge, I don't just how to select the cells with the formular and have it replaced by the number.

Thanks

Alex