Hello, I have created the macro below. I recorded it so that when I tap the button the macro is attached to it inserts a new row above my designeted cell (IMP_01). One of the cells in the new row also has a formula in it which makes it equal 2 of the other cells in the row multiplied by each other. The problem is the first time I run the macro everything works fine. The second time I run the macro the formula does not work/appear in the new row. In addition, I am using this macro and other similar ones to add new rows and 3 different sections of the same spreadsheet.

Any help would be awesome, thanks!

Range("IMP_01").Select

Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

Range("E29").Select

ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"

Range("IMP_01").Select

End Sub

First, a posting tip... Please click on the

blue lineat the end of this post and read the instructions on how to post VBA code in this forum.As for your question, your code selects E29 and puts the formula in that cell. That's all it's ever going to do since you've hardcoded that range reference. It's going to put the formula in E29 every time you run the macro.

In addition, you do not have to Select a range via VBA to perform an action on it. That's very inefficient. You can almost always perform the operation directly on the range within VBA.

Try this. It inserts a row above the Named range and then uses the Row property of the Named range to determine where to put the formula. Basically you are letting VBA figure out what row to put the formula in instead of telling it to put the formula in a specific cell.

Sub InsertFormula() 'Insert row above named range Range("IMP_01").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'Put formula in new row Range("E" & Range("IMP_01").Row - 1).FormulaR1C1 = "=RC[-2]*RC[-1]" End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Awesome!

Thank you so much for your response! It was very helpful and completely solved my problem! I will make sure to post correctly next time.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History