Converting a VBA output String to a Cell Reference

Hi All,

I'm a little new to this, but im sure i'm pretty close to the right thing.

I have written a VBA function which returns a string, this string is actually a cell reference/range, for example "A1:A22".

I then want to use the result of this function, in the AVERAGE() function of excel. For example AVERAGE(classAverage(256)).

Now I have tested and if I just put in a cell, =classAverage(256), the cell then shows the result of A1:A22.

However once this function is put inside the AVERAGE function in excel, it fails and just displays #VALUE!. Upon debugging where the issue is, it shows that the AVERAGE function is using AVERAGE("A1:A22") instead of AVERAGE(A1:A22) which I need it to be for this formula to work.

Is there any way that I can have the Average function of excel take the string formatted output from my own VBA function? ie by removing the " (quotation marks)

What kind of datatype is the AVERAGE function looking for in regards to a range of cells, I have tried String and Range.

Any suggestions are appreciated.

EDIT: Please see second post - Thanks

You might look at the INDIRECT function.

=AVERAGE(INDIRECT("A1:D34")) = AVERAGE(A1:D34)

ADDRESS might also be useful as in

=AVERAGE(INDIRECT(ADDRESS(ROW(classAverage(x)),COLUMN(classAverage(x)),1,TRUE,"data")))

Your help is very much appreciated mikerickson

I never would have found the INDIRECT function myself, it's working an absolute charm. In case you wanted to see it, this is my final formula, and it works.

=IF(INDEX('[0703 March Generic.xls]Sheet1'!$A$7:$AC$275,MATCH($A$10:$A$29,'[0703 March Generic.xls]Sheet1'!$A$7:$A$275,0),12)/100=0,AVERAGE(INDIRECT("data!"&classAverage(MATCH($A$10:$A$29,'[0703 March Generic.xls]Sheet1'!$A$7:$A$275,0))))/100,INDEX('[0703 March Generic.xls]Sheet1'!$A$7:$AC$275,MATCH($A$10:$A$29,'[0703 March Generic.xls]Sheet1'!$A$7:$A$275,0),12)/100)

I just need some more refining of my VBA code for the classAverage function, and it should all be sweet, so thankyou so much!

As a brief explaination about what I am doing, I am calculating Financial fund returns from a raw data sheet, however some, because the fund was not yet active have a return of 0. The classAverage function takes all the data from the other financial funds in the same class, ie international shares, and averages them. The "bounds" of what makes a financial class on the raw data sheet are two horizontal yellow coloured rows. So the classAverage function finds these boundries for the class and returns the range. This is to give an approximated return value for a fund which was not yet open yet. Dunno if that makes any sense to you, but might be interesting to know where all of this was being applied.

Cheers once again.

Pretty much spot on mikerickson, its an estimation of past performance, based off other funds in the same market segment.

EDIT: Should I be posting this under a new thread in the worksheet forum?

One more thing I am stuck on....

=IF(INDEX('[0703 March Generic.xls]Sheet1'!$A$7:$AC$275,MATCH($A$10:$A$29,'[0703 March Generic.xls]Sheet1'!$A$7:$A$275,0),21)/100=0,AVERAGE(INDIRECT("data!"&classAverage(MATCH($A$10:$A$29,'[0703 March Generic.xls]Sheet1'!$A$7:$A$275,0),"U")))/100,INDEX('[0703 March Generic.xls]Sheet1'!$A$7:$AC$275,MATCH($A$10:$A$29,'[0703 March Generic.xls]Sheet1'!$A$7:$A$275,0),21)/100)

I've searched and found pieces of code which allow the Average formula to ignore 0 values from the calculation for example from:
http://portal.law.pace.edu/metadot/i...wsitem&op=show

Follow these steps:

1. Enter the following formula: =AVERAGE(IF(B3:B23<>0,B3:B23))

However when I attempt to apply the same theory to my code it always fails, I've been going over it for a while now and I cant see where I might be missing any brackets etc... Maybe I just need a fresh mind.

Applying the same principal as the code above I have been replacing the AVERAGE() calculation in my code with:

AVERAGE(IF(INDIRECT("data!"&classAverage(MATCH($A$10:$A$29,'[0703 March Generic.xls]Sheet1'!$A$7:$A$275,0),"U")))<>0,INDIRECT("data!"&classAverage(MATCH($A$10:$A$29,'[0703 March Generic.xls]Sheet1'!$A$7:$A$275,0),"U"))))/100

Which fails, If you dont want to help I understand... far too many brackets for my liking.
If you want to have a go at fixing it up for me, please do!