LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 28th 04, 01:27 AM
Huge project
Posts: n/a
Default Help with macro formula and variable

OK, here is what I am doing...

I am opening up a csv file, forcing the B-column to be text. I copy all the
data from there into another excel sheet. I use a macro to turn that data
into a pivot table. This table varies in rows and columns depending on the
amount of data. I want to put an average formula at the bottom of each
column that only averages the above cells that are in a certain value range
(example: average everything between 5 and 45). I know this has to be done
with an array formula. The problem I have is getting the macro to put the
formula in the sheet. I need the macro to be able to change the range that
it puts in the formula to suit the number of rows(example: sometimes the
range will be B5:B30 other times B5:B45). I already have the values 30 or 45
from the example counted in the macro, can I use them as a variable in the
formula somehow? Once I get the macro to do this part correctly then I need
to be able to fill across the formula to the end of the columns. After that,
I need to put in a rank formula under the average formula to rank all of the
averages and fill that across. It would be nice to have the averages/rank
update if I need to turn off any columns in the pivot table. Maybe I should
do that with a seperate macro? I realize this is a lot to ask for help on
but if I could just figure out how to have the macro put a different range in
the formula that would be a huge help. Thanks to everyone for all of their


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add 2nd variable to if statement - ifAnd? Todd F. Excel Worksheet Functions 4 December 18th 04 07:49 PM
Cell reference - for the sheet name, can I use a variable? Matt Lawson Excel Discussion (Misc queries) 4 December 13th 04 02:31 PM
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. [email protected] Excel Worksheet Functions 2 December 11th 04 12:05 AM
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable [email protected] Excel Worksheet Functions 2 December 11th 04 12:00 AM
Indirect references in a linked formula Markshnier Excel Worksheet Functions 1 November 15th 04 02:49 AM

All times are GMT +1. The time now is 08:01 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"


Copyright © 2017