Unable to get average property of Worksheetfunction class

[fedds2]

I am trying to calculate average and SD from data which has different start points on a worksheet. The data is in column I and the first set starts at I2 and varies where it finishes. The second set starts 6 rows below where the first set ends.

Cells(2,16).Value = Worksheetfunction.Average(Range("I2:I"&NewEnd))
Cells(3,16).Value = Worksheetfunction.StDev(Range("I2:I"&NewEnd))
These two lines work fine

Cells(2,17).Value = Worksheetfunction.Average(Range("I" &RwStart&":I"&NewEnd2))
Cells(3,17).Value = Worksheetfunction.StDev(Range("I"&RwStart&":I"&NewEnd2))

RwStart and NewEnd2 both calculate fine so the problem is in the Range. How should the I be incremented to the correct start point?
What are the rules for the use of " and &?

cheers
Fedds2

 

[jeffreybrown]

Fedds,

I think you just need to add some spaces inbetween the named range and the & sign (tested)

WorksheetFunction.Average(Range("I" & RwStart & ":I" & NewEnd2))

 

[fedds2]

Hi Jeff,
Thanks for the thoughts. I tried that and it still comes up with the error.
I'm think i'm not defining that first bit correctly ie the Range("I" & RwStart &":I"

Fedds

 

[fedds2]

Arrrrgh - disregard this post as the formula is correct. I am using multiple sheets and on careful checking it is failing on certain sheets becasue of the data. Sorry to waste your time.

 

[mvptomlinson]

Fedds, try stepping through your code using F8 in the VB Editor window, and when it gets to the lines in question (before pressing F8 to go past them) hover your mouse over the two variables RwStart and NewEnd2. Check to make sure they contain values as expected. If not, they're probably setup incorrectly. If you post your full code it might help figure it out, too.

 

[jeffreybrown]

No trouble at all