My Assistant
Site Message
(Message will auto close in 2 seconds)
 Locking Columns Used In Average(), Office 2007
|
|
 Mar 3 2012, 02:02 PM
Post
#1
|
|
|
New Member Posts: 8  |
I have a spreadsheet where I want Column D to show the Average for columns E thru P for a row. I need to be able to add a new column (i.e. insert a new column between D and E, thereby making the existing E become F) without the the Average function in D changing - I don't want it to change and now show Average for F thru Q but to continue to show Average for E thru P. I am rather new to using VB in Excel but figure there must be some way to do this. Any help would be greatly appreciated.
|
 |
 
|
 |
Replies
|
Mar 4 2012, 02:46 AM
Post
#2
|
|
|
UtterAccess Ruler Posts: 1,090 |
you can try this code:
CODE ' ' Courtesy of Arnel G. Puzon ' Somewhere Out There ' Function fMyAverage() Dim strRange As String Dim objRange As Range Application.Volatile strRange = "E1:P" & ActiveSheet.UsedRange.Rows.Count Set objRange = Range(strRange) fMyAverage = WorksheetFunction.Average(objRange) End Function just put it on any cell, say in A1, =fMyAverage. |
|
|
|
Posts in this topic
kesmithjr Locking Columns Used In Average() Mar 3 2012, 02:02 PM
dflak As far as I know this should happen automatically.... Mar 3 2012, 04:29 PM kesmithjr Maybe I didn't make it clear but I want the ra... Mar 3 2012, 04:58 PM kesmithjr Arnel, Appreciate your assistance. The code you p... Mar 5 2012, 08:17 AM
arnelgp You got it , but you have left out one important t... Mar 5 2012, 08:45 AM |
 |
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 22nd May 2013 - 07:07 PM |