Fixing equaton to cell address despite inserting rows!

[Niemsters]

Hey guys, first time poster, I've got to say for every question answered, there are probably 20+ people that it helps, so good job making the world a better place!

My problem:
my worksheet includes 24 rows of data (Dates in Column A, Data in Column B). My macro inserts a row (at A4) and pastes data in B4 which is in a range used for an equation in F3 (=100*STDEV($B$4:$B$15)). However when I instert a row it pushes the equation to look at B5:B16 instead of staying locked on B4:B15.

I'm not sure if I need to fix this with VBA or just in Excel... either's fine with me.

My Code:
'Insterst row and auto fills date to new row
Range("A4").Select
ActiveCell.EntireRow.Insert
Range("A5:B8").Select
Selection.AutoFill Destination:=Range("A4:H9"), Type:=xlFillDefault
'Copeis Data
Sheets("Sheet1").Select
ActiveCell.Offset(0, 6).Select
Selection.Copy
Sheets(Sheet2).Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Any help is appreciated, I'm sure it's a pretty simple problem.

 

[Sandeep Warrier]

Hi & welcome to the Board!

The easiest way, AFAIK, since you are already using a macro, would be to code the formula in the macro.

For example, try inserting the line

Range("F3").Formula = "=100*STDEV($B$4:$B$15)"

in your code.

 

[jasonb75]

Welcome to the board!

My personal choice would be change the formula in F3 to

=100*STDEV(INDIRECT("B4:B15"))

I'm sure others will have some alternative solutions for you.

 

[Niemsters]

Both worked great! I appreciate the help!