Fixing equaton to cell address despite inserting rows!

Niemsters

New Member
Joined
Jul 29, 2010
Messages
5
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
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

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

in your code.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,961
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,720
Messages
5,660,511
Members
418,581
Latest member
gtarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top