Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 23rd, 2010, 08:34 PM
Authorized User
 
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Unhappy Code for average value of 24 files in one new file

Hey all! I really need some help. Here's my situation. I have 24 different workbooks that have the same exact set up with different values. I need to find the average of each cell in the 24 different files and place the new value in an identical spreadsheet already set up.

The other problem is that there are separate sections so I would need some kind of loop to skip the blank cells and continue checking for values.

Can you PLEASE help.

Thanks,
Melissa
 
Old August 24th, 2010, 01:34 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi Mellisa

You can achieve that using Functions. For example,

=AVERAGE('C:\ShasurData\[Book1.xlsx]Sheet1'!$A$2:$A$6,'C:\ShasurData\[Book1.xlsx]Sheet1'!A2:A6)

would find the average of all cells between A2 to A6 from both workbooks. It will also ignore Blank rows.

HTH

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old August 24th, 2010, 09:12 AM
Authorized User
 
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you, but these files might be moved to a new folder, and when I tried that, after I enter the files a window prompts me to click on the file to update it. Am I going to have to open all the files every time I want it to update?

Thanks,
Melissa
 
Old August 24th, 2010, 10:59 AM
Authorized User
 
Join Date: Sep 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also, the average function only works for up to 18 and says it is too long to complete for 24 different files. Is there some other way to do this?
 
Old August 24th, 2010, 10:20 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

You can take the average twice of twelve excels each and then take an average of the two

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 24 Remoting code is missing eschmidt BOOK: Professional VB 2005 ISBN: 0-7645-7536-8 3 February 16th, 2008 09:37 PM
Getting an average using SQL vs VBScript mat41 Classic ASP Professional 1 November 1st, 2007 06:33 PM
Source code for Ch 24 in VB6? cJeffreywang BOOK: Professional ASP.NET 2.0 and Special Edition; ISBN: 978-0-7645-7610-2; ISBN: 978-0-470-04178-9 1 July 26th, 2007 11:52 AM
Code of Chapter 24 qazi_nomi BOOK: Beginning ASP 3.0 0 June 23rd, 2004 11:48 PM
7Day Moving Average Gezza SQL Language 0 November 21st, 2003 02:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.