Solved: averaging variable sized ranges

[divingdingo]

back to wind data !!

if i want to average a series of wind speeds, then i can write something like this:

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C[-2]:R[31833]C[-2])

which averages the numbers in whichever column i happen to select relative to the activecell.

however, the ranges i'm dealing with change length. some arrays have 31833 rows in (as above) however others have twice that.

i've been learning something in the last week as i can now set a variable which can hold the number of rows that is in an usedrange. my promlem is that i'm having trouble writing the correct code to do the above average but using a variable instead of a number.


this is what i have tried, but none work

ActiveCell.FormulaR1C1 = "=AVERAGE(R[-2]C[-19]:R[CStr (i)]C[-19])


where i is the variable.

could anyone kindly suggest the correct way of writing this in VBA

mark

[rory]

You can use:
[vba]ActiveCell.FormulaR1C1 = "=AVERAGE(R[-2]C[-19]:R[" & i & "]C[-19])"
[/vba]

PS I assume i is in fact declared as a Long and not an Integer?

[divingdingo]

thank you rory.

i'm still having problems with writing it in VBA without the dreaded yellow highlighter coming out and stopping the procedure!

i'm trying to assign the variable fiftyA to equal the average on a different sheet.

this is my code, which is oh so wrong!

fiftyA = AVERAGE(sheet1!R[-1]C[-3]:R[" & m & "]C[-3])

where m is a variable as LONG and Sheet1 is weher all the data is.

any ideas?

mark

[rory]

You can't just put worksheet formulas into code like that. You can use:
[VBA]fiftyA = Application.Average(range)[/VBA]
but it's not clear to me from your example what actual range you want to average - is it relative to the actual cell or is it a specific location?

[divingdingo]

You can't just put worksheet formulas into code like that. You

damn it, so that's why it wasn't working too well!

so it looks like i need to set a range from sheet1, then average that range using the above code.

i'll try that, thanks

mark

[divingdingo]

okay, so i'm still going round in circles!!

this is the bits of my code so far which relates to what i'm trying to do



Range("C2:C" & m).Name = "fifty"'this sets the name of the range

fiftyA = Application.Average("fifty") 'this is adapted from you above

Range("b2").Select
ActiveCell.FormulaR1C1 = fiftyA 'this is the output cell


not sure what i'm doing wrong. if you have any other ideas why i can't work this out (other than i'm a VBA debutant!!)

thank you for your help

[Bob Phillips]

[vba]

Range("C2:C" & m).Name = "fifty" 'this sets the name of the range

fiftyA = Application.Average(Range("fifty")) 'this is adapted from you above

Range("b2").Value = fiftyA 'this is the output cell
[/vba]

[rory]

If you want it entered as a formula, you can use:
[VBA]Range("B2").FormulaR1C1 = "=AVERAGE(sheet1!R[-1]C[-3]:R[" & m & "]C[-3])"
[/VBA]

[divingdingo]

[vba]

fiftyA = Application.Average(Range("fifty")) 'this is adapted from you above

[/vba]

i can't believe i was one set of brackets out !! vba can be very frustrating sometimes.

thank you for helping me find the solutions to my problems and taking your time to do so.

[Bob Phillips]

You also missed Range!


__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

[divingdingo]

You also missed Range!

opps, so i did !! and for a moment there i had my vba confidence up...!

thanks for noticing that i'm worse than i thought!

[Bob Phillips]

That's quite alright it's my job.