How do i calculate the average between cells using VBA?

[10121730]

Hi all.
wondering if you can help me. I'm running out of time with my tutorial and struggling. complete newbie.. this is what im after...
i have been asked to work out the average mark out of 100 of 4 assignments with a 20 % weighting and an average mark out of 2 exams with an 80% weighting. i made the spreadsheet with all the results etc in so i need to use vba to write the code to work these out. theres a pile more but if someone could please give me a helping hand just with thsi bit hopefully i will be able to work out the rest.

I will attach my spreadsheet, very simple but im also very new!

thanks!
Excel VBA Tutorial.xlsm

[dilipandey]

Hi,

I have given the calculation formulas in the attached sheet, please review them and let us know if this is fine and then we can further proceed with VBA. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[10121730]

Thanks for that.
That looks like the kind of thing i need yes, apart from the grading i guess will need select case from A-F or something of the sort

this is the code i have come up with so far but i am getting error "object error 424" object required.



Function assignmentaverage()

Range("G2").Value = Application.Average(Range("C2:F2")).Value
Range("G3").Value = Application.Average(Range("C3:F3")).Value
Range("G4").Value = Application.Average(Range("C4:F4")).Value
Range("G5").Value = Application.Average(Range("C5:F5")).Value

End Function


hope you can help

*U P D A T E*

I Have now worked this out as :

Range("G2").Value = Application.WorksheetFunction.Average(Worksheets("sheet1").Range("C2:F2"))
Range("G3").Value = Application.WorksheetFunction.Average(Worksheets("sheet1").Range("C3:F3"))
Range("G4").Value = Application.WorksheetFunction.Average(Worksheets("sheet1").Range("C4:F4"))
Range("G5").Value = Application.WorksheetFunction.Average(Worksheets("sheet1").Range("C5:F5"))

however i still need a weighting of 20% on the assignment average and 80 on the exam... cant seem to do this..

[Bob Phillips]

Why VBA, why not just use formulas?

[10121730]

the reason i am using vba is because that is what i've been asked. It's all to do with getting us familiar with vba as we will be moving onto things that can only be handles by vba and not by formulas within excel.

[jaslake]

Hi 10121730
Please use code tags around your code.

[Bob Phillips]

Hmmm! One simple statement that uses a worksheet function, can't see that helping to get you familiar with VBA!