I need a macro

I would like a macro to do the following:
I would place the the cursor in any cell and this macro would give me the 
average of all the entries to the left if the selected cell. EX: I place the 
cursor in cell M12.
I want this macro on display in cell M12 the average of all the values from 
Col C12 to Col L12. Note that there may NOT be entries in all the cells in 
that range. Also the start point would always be col C .Thanks 
0
pcor (148)
3/31/2008 5:33:00 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
462 Views

Similar Articles

[PageSpeed] 56

You can do this easily with a worksheet_selection event in the sheet module 
but you would probably want to restrict to a certain range or columns and 
rows or it would fire EACH time you select ANY cell. Never less than col C 
and never more than column _____? rows________?

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"pcor" <pcor@discussions.microsoft.com> wrote in message 
news:DD5BBC5B-F159-4550-97F9-5936F351F541@microsoft.com...
>I would like a macro to do the following:
> I would place the the cursor in any cell and this macro would give me the
> average of all the entries to the left if the selected cell. EX: I place 
> the
> cursor in cell M12.
> I want this macro on display in cell M12 the average of all the values 
> from
> Col C12 to Col L12. Note that there may NOT be entries in all the cells in
> that range. Also the start point would always be col C .Thanks 

0
dguillett1 (2487)
3/31/2008 5:42:52 PM
Thanks for thr quick reply....BUT can you please tell me HOW to accomplish 
that!
Thanks

"Don Guillett" wrote:

> You can do this easily with a worksheet_selection event in the sheet module 
> but you would probably want to restrict to a certain range or columns and 
> rows or it would fire EACH time you select ANY cell. Never less than col C 
> and never more than column _____? rows________?
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "pcor" <pcor@discussions.microsoft.com> wrote in message 
> news:DD5BBC5B-F159-4550-97F9-5936F351F541@microsoft.com...
> >I would like a macro to do the following:
> > I would place the the cursor in any cell and this macro would give me the
> > average of all the entries to the left if the selected cell. EX: I place 
> > the
> > cursor in cell M12.
> > I want this macro on display in cell M12 the average of all the values 
> > from
> > Col C12 to Col L12. Note that there may NOT be entries in all the cells in
> > that range. Also the start point would always be col C .Thanks 
> 
> 
0
pcor (148)
3/31/2008 6:08:23 PM
Let's see, you didn't answer my questions, did you?

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"pcor" <pcor@discussions.microsoft.com> wrote in message 
news:B6250251-4A0C-4993-A99E-20438B890BE4@microsoft.com...
> Thanks for thr quick reply....BUT can you please tell me HOW to accomplish
> that!
> Thanks
>
> "Don Guillett" wrote:
>
>> You can do this easily with a worksheet_selection event in the sheet 
>> module
>> but you would probably want to restrict to a certain range or columns and
>> rows or it would fire EACH time you select ANY cell. Never less than col 
>> C
>> and never more than column _____? rows________?
>>
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "pcor" <pcor@discussions.microsoft.com> wrote in message
>> news:DD5BBC5B-F159-4550-97F9-5936F351F541@microsoft.com...
>> >I would like a macro to do the following:
>> > I would place the the cursor in any cell and this macro would give me 
>> > the
>> > average of all the entries to the left if the selected cell. EX: I 
>> > place
>> > the
>> > cursor in cell M12.
>> > I want this macro on display in cell M12 the average of all the values
>> > from
>> > Col C12 to Col L12. Note that there may NOT be entries in all the cells 
>> > in
>> > that range. Also the start point would always be col C .Thanks
>>
>> 

0
dguillett1 (2487)
3/31/2008 6:12:25 PM
it could be asked for from any col past M but always starting at col C
thanks

"Don Guillett" wrote:

> 
> Let's see, you didn't answer my questions, did you?
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "pcor" <pcor@discussions.microsoft.com> wrote in message 
> news:B6250251-4A0C-4993-A99E-20438B890BE4@microsoft.com...
> > Thanks for thr quick reply....BUT can you please tell me HOW to accomplish
> > that!
> > Thanks
> >
> > "Don Guillett" wrote:
> >
> >> You can do this easily with a worksheet_selection event in the sheet 
> >> module
> >> but you would probably want to restrict to a certain range or columns and
> >> rows or it would fire EACH time you select ANY cell. Never less than col 
> >> C
> >> and never more than column _____? rows________?
> >>
> >> -- 
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> dguillett1@austin.rr.com
> >> "pcor" <pcor@discussions.microsoft.com> wrote in message
> >> news:DD5BBC5B-F159-4550-97F9-5936F351F541@microsoft.com...
> >> >I would like a macro to do the following:
> >> > I would place the the cursor in any cell and this macro would give me 
> >> > the
> >> > average of all the entries to the left if the selected cell. EX: I 
> >> > place
> >> > the
> >> > cursor in cell M12.
> >> > I want this macro on display in cell M12 the average of all the values
> >> > from
> >> > Col C12 to Col L12. Note that there may NOT be entries in all the cells 
> >> > in
> >> > that range. Also the start point would always be col C .Thanks
> >>
> >> 
> 
> 
0
pcor (148)
4/1/2008 2:44:00 AM
Right click sheet tab>view code>insert this>adjust columns/rows>SAVE 
workbook.
Now if you select a cell within the allowed area you will get your average.
Please note that you will have difficulty putting info into the cells so you 
may want to comment out the macro to do that.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row <12 Or Target.Row > 25 _
Or Target.Column < 3 Or Target.Column > 14 Then Exit Sub
tr = Target.Row
tc = Target.Column - 1
MsgBox Application.Average(Range(Cells(tr, 3), Cells(tr, tc)))
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"pcor" <pcor@discussions.microsoft.com> wrote in message 
news:DD5BBC5B-F159-4550-97F9-5936F351F541@microsoft.com...
>I would like a macro to do the following:
> I would place the the cursor in any cell and this macro would give me the
> average of all the entries to the left if the selected cell. EX: I place 
> the
> cursor in cell M12.
> I want this macro on display in cell M12 the average of all the values 
> from
> Col C12 to Col L12. Note that there may NOT be entries in all the cells in
> that range. Also the start point would always be col C .Thanks 

0
dguillett1 (2487)
4/1/2008 12:37:03 PM
Reply:

Similar Artilces:

uploading a website, dumb stuff, but I need help
This is my first time, and go figure, Verizon is no help (my ISP). How can I make it work. I'm dumfounded. I created the html files in Publisher, but when I upload them using WS FTP, only the default (1st) page works, and none of the links work. How about you tell us what version of Windows and Publisher you are using? -- I'm going to guess you are using version 2002. I recommend you review my 2002 tutorial at www.davidbartosik.com/ppt.htm I also recommend you use our forum at microsoft.public.publisher.webdesign -- David Bartosik - Microsoft MVP Visit www.davidbartosi...

2003 xls(macro) occured error when opening with office 2007
hi! Everyone. Sorry, I'm not used to English. ^^; I have xls(with macro-Office2003 made) i changed office version 2003 to 2007.. (xls) macro code has "thisworkbook.close" when i called thisworkbook.close method... all xlsx, xls opend files closed with error... event ID 1000 . how can i solve this error... i need thisworkbook.close action.. -- Everyday is a little life!! Thank u!! Hi, No problem with the excellent English. Please post the code. Mike "HOONHEE" wrote: > hi! Everyone. > Sorry, I'm not used to English. ^...

Downloaded "Fitness Chart for Males" need it in metric?
The template is set in imperial measurement, is there a metric version available? Hi, This says it metric. http://office.microsoft.com/en-gb/templates/TC010762691033.aspx?CategoryID=CT010482871033 Cheers Andy Bewildered wrote: > The template is set in imperial measurement, is there a metric version > available? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

OT: Anybody in here do much with COM? Need to know what newsgroups...
....to use to post the following question: "What is the relationship between deploying and registering a typelib for your COM components and a proxy for said components?" Thanks, WTH "WTH" <nospam@spamtheman.com> wrote in message news:e88StatPFHA.3076@tk2msftngp13.phx.gbl... > ...to use to post the following question: > > "What is the relationship between deploying and registering a typelib for > your COM components and a proxy for said components?" > > Thanks, > > WTH > > Try the microsoft.public.vc.atl newsg...

Need Great Plains/FRx Forecaster helpin Bay Area-full/parttime/eve
My company is looking for technical and functional people in Great Plains or in FRx Forecaster. The individual should be local in the California Bay Area/Silicon Valley We are flexible and will consider full time/part time/ evenings/week end schedules. Send resumes directly to greatplainsuser@gmail.com ...

ps...i only need powerpoint but..
Will i have access to all the office programs? And how/where is the agreement section and activation wizard it tells me about?!! Thanks! Well, it all depends on what you buy, doesn't it? "jasminegirl99" <jasminegirl99@discussions.microsoft.com> wrote in message news:B774DED7-FB1C-4FD0-8FDF-E5A8C8937EB8@microsoft.com... : Will i have access to all the office programs? And how/where is the : agreement section and activation wizard it tells me about?!! : : Thanks! ...

need to sync a pda with a shared calendar
i need some advice ive been asked to come up with a way to create a shared calendar on exchange2003 for my department. and then be able to sync it with our pda's someone on here sometime ago mention MasterCal is there any advice on this i this what i realy need is a way to sync a calendar in the public folders with my on calender and then i can syn it with my pda any ideals "James" <jgrace@digitelusa.net> wrote in message news:etQMeloeEHA.3632@TK2MSFTNGP09.phx.gbl... > i need some advice > ive been asked to come up with a way to create a shared calendar on &g...

Question about macro
Here is the case: Every week I will receive ten files(each has one work sheet) from my brokers. All the files are in the same format but different size. Is there a way to automatically collect the data from each file every week and put them all in one work sheet? The files name will change weekly(to accomodate the date) and so will the size. Thanks in advance. Jason ...

Public Folder Replication Problem [need help]
I'm 90% done migrating my Exchange 2003 server to new hardware, I'm having trouble finishing this step - replicating the Public Mail Store.. I've added the new server to the replication tab in ESM, the folders are showing up under the new server, and are in sync! However in ESM when I go Administrative Groups -> First Admin Group -> Folders -> Public Folders, there is a list here.. this list is what shows on Outlook clients under "public folders".. when I right click this folder and say Connect to.. and select the new server's public mail store, I get ...

Macro to find value in worksheet
I've been asked if I could come up with a macro or vba that I could enter a list of names in the code and run the macro to find any matches of the names in the open worksheet. I'm a newbie at this and I'm lost. Is there anyone who could help me with this code? It seems like such a simple task but I know better. I'd have to enter about 100 names individually and do a find every day to see if certain names were in this sheet. The sheet changes every day, the names don't. I'd rather have the names hard coded in the macro and just run it and have it pop up a message say...

GP Needs a More Unified Interface
All GP message boxes that have OK or OK/Cancel buttons need to have <Enter> and <ESC> do the same things, just like every Microsoft Office Platform application. Also, along the same thread, the GP UI needs to be easier to use with common shortcut keys as typical to every Windows and/or Office platform application, such as Ctrl+S for Save. This just needs to be cleaned up drastically. One other thing - in the Sales Transaction Entry window in GP 10, there is a way to get trapped in the Currency Code text box. If you do not enter a document number/customer ID and then fill ou...

money ecountered a problem and needs to close
I have windows XP and money standard 2002 and never had problems until just recently money stopped opening and gave me the error message "Microsoft Money has encountered an error and needs to close. We are sorry for the inconvenience". The error report reads AppName: msmoney.exe AppVer: 10.0.0.731 ModName: kernel32.dll ModVer: 5.1.2600.2180 Offset: 0001861d. I have tried uninstall and reinstalled and no better. Any help In microsoft.public.money, tangodizzy wrote: >I have windows XP and money standard 2002 and never had problems until just >recently money ...

Macro to Protect workbook using a password
I know this would be a little unorthodox, but I currently have a macro that will unprotect a workbook including a password. The line is as follows: ActiveWorkbook.Unprotect (password) I also want a macro that will do just the opposite, including the password. Is this possible? I've tried the following with no luck. ActiveWorkbook.Protect (password), Structure:=True, Windows:=True What does "no luck" mean? Your code works fine for me, though there's no need for the parentheses around the password. In article <Od7KsopkDHA.744@tk2msftngp13.phx.gbl>, "PCLI...

I need help in creating a chart
I have the following data that I am trying to get into a chart and it is not working. Ideally, I would like the height of the jumps going up the left side of the graph, the physical heights along the bottom and the columns to be labeled on the side as the person's name. But, I can't figure out how to put in that one height jumped so high... Name of Person Physical Height Height of Jump Codi 4'11 40" Rosie 5'0 29" Sammy 5'4 ...

Q: Excel says there's a macro, but there isn't...
Using Excel 2000. A coworker has created a spreadhseet that, when I try to open, it tells me that there are macros and asks if I want them enabled or disabled. I click on tools | macro | Macros and there's no macro there. I click on tools | macro | VBE and there's no modules attached. So, what's going on and how do I get rid of it? This seems to be a problem with this one coworker's Excel program. Thanks! -Mark > I click on tools | macro | Macros and there's no macro > there. Most likely the macro has been removed but not the "parent" module. ...

Sort Macro
I would like to be able to sort the same column in all worksheets in a workbook. I found two macros that were similar in this discussion group but have not been able to make them work. The key column in each worksheet has a different number of rows but it is always the same column. Thanks in advance for your assistance. what have you done so far to make them work? if you pasted them in the vb editor then tools>macro>macros>select macro>run should do it. or alt+F8>select macro>run >-----Original Message----- >I would like to be able to sort the same column in al...

Chart Macro Help Needed
Hello, I have a chart named "VR" located on it's own tab, where a user can go and, via drop down box, select data range A or data range B to view on the graph. I've used this for a long time successfully, but now I have a need where the data scale is disparate between the two, and I need to set specific ranges for the Y axis based on the selection of view A or view B. the data is on a separate sheet, and I have formulas in place to return the correct Y scale range based on the selection of A or B, and have named the output of those Y1min for the Y axis minimu...

Pivot Table Filtering
Hi, I am trying to filter a pivot table through a macro and what I have works but I have come to find out that the source data is more dynamic than I thought and is breaking my macro each month I run it. Here is a small exerpt of what I have so far: ActiveSheet.PivotTables("PivotTable").PivotFields("Project ID").CurrentPage = _"(All)" 'Filter for KT Hours With ActiveSheet.PivotTables("PivotTable").PivotFields("Project ID") .PivotItems("7012276").Visible = False .PivotItems("7012279").Visib...

vba macro to perform custom reporting -transpose from rows to columns
I have one workbook and two sheets 1 sheet has data for automated test scenarios that may be run up to 3 time if they don't pass on the first or second try Sheet1: TestSet Test Result reason if failed 1 pass 2 pass 3 pass 4 fail x 4 fail y 4 fail z I want to create a summary report that looks like Sheet2 for the Sheet1 logs above test run1 result test run2 result test run 3result Sheet2 Testset result1 result2 result3 1 pass 2 pass 3 pass 4 fail fail fail So if a testset is executed more ...

Need help Outlook command line options
I'm trying to find out how to launch a new mail message in Outlook from a command line. I looked in the help and found the list of command line options, but I'm having problems trying to combine them. Here's a list of commands I'm using: Open a new mail message: outlook.exe /c ipm.note Open a new mail message and populate sender: outlook.exe /c ipm.note /m someone@microsoft.com Open a new mail message with attachment: outlook.exe /c ipm.note /a filename All of these work fine, but when I try to specify a recipient e-mail address and an attachment, I get an error messag...

Macro to link Sheets to main workbook
Hi All, I want to link 5 seperate work sheets into one workbook that will update automatically. I have been told this is done with a macro? Anyone have any ideas? Raven. ...

Need chart generating advice!
Hi, Let me thank you in advance for any assistance you may provide. I am new to making charts and am not having much luck using the M Excel help. I need to make a chart displaying the following data: I have 5 products that I invited people to put in order of importanc to them. I now want to create a few charts to display the information I have the excel spread sheet set up where Column A is numbered 1 thr 5, across the top (Row 1) I have listed the names of all respondents. Below each name I have listed the products in the order of thei preference. How can I now use this data? I need to...

Excel 2003
Want macro to run in personal.xls upon starting Excel... Put this in the ThisWorkbook code module of personal.xls: Private Sub Workbook_Open() MyMacro End Sub where MyMacro is the name of your macro which is stored in a regular code module in personal.xls. In article <i6s8rv0m3nimrpadm167j1qjo40kd4au47@4ax.com>, flumpf <clydesdale@yahoo.com> wrote: > Want macro to run in personal.xls upon starting Excel... ...

Why do I need a X400 address?
Under E-Mail address policy I'm not able to remove the X400 address. Why do I still need a X400 address for every mail enabled object? Is it only for internal purposes? There are Exchange process that use X.400 for internal/external mail routing. "Vincent Vetsch" <Vincent Vetsch@discussions.microsoft.com> wrote in message news:F0B5BBA4-D3C1-4BBC-B3DB-5473A93735D6@microsoft.com... > Under E-Mail address policy I'm not able to remove the X400 address. Why do I still need a X400 address for every mail enabled object? Is it only for internal purposes? ...

Record Macro
We are using Excel 2003. Every month we will import data from a database of our MS SQL 2000 server to create a report. Basically, this procedure is repetitive every month and we wonder if we could make use of the "Record Macro" function to record down the whole procedure such that every month we will just need to run the macro, without having to repetitive the same steps over and over again every month. However, as we are still not familiar of playing around with Macros, we experience some problems such as, since the data that we import from the database is according to the ...