Solved Simple formula is not working in macro

Microsoft Excel 2010 - complete product...
October 1, 2012 at 08:26:38
Specs: Windows 7

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!

Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub

See More: Simple formula is not working in macro

Report •

October 1, 2012 at 10:11:36
✔ Best Answer
First, a posting tip...

Please click on the blue line at 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.

Report •

October 1, 2012 at 11:00:19
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.

Report •

Related Solutions

Ask Question