PDA

View Full Version : Solved: Modifying code to select specific data



mgoto125
04-03-2012, 05:30 AM
Hello all,

I have an excel file with data and I would like to extract data from specific cells, add them to another column, total it up and find the average of the values.

I have recorded a macro to do this, but not sure how I can do this for the real file which has over 10000 lines. Sorry I am not to programming and VBA. I have posted the recorded macro of what I am trying to achieve and also uploaded the excel file with the macro and my rather poor attempts and using loops. Thanks everyone.

Sub Macro6()

Range("A5").Select
Selection.Copy
Range("G1").Select
ActiveSheet.Paste
Range("A12").Select
Application.CutCopyMode = False
Selection.Copy
Range("G2").Select
ActiveSheet.Paste
Range("A19").Select
Application.CutCopyMode = False
Selection.Copy
Range("G3").Select
ActiveSheet.Paste
Range("G5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)"
Range("G6").Select
End Sub

Bob Phillips
04-03-2012, 05:45 AM
Why not just use a formula

=AVERAGE(A:A)

mgoto125
04-03-2012, 06:52 AM
Sorry just check the original text file, please see attached, as you can see there are other numbers mixed in the column which I do not want to consider in the average.

Any further input would be much appreciated.

Thanks

Bob Phillips
04-03-2012, 09:58 AM
What determines what gets included? if it is the 0.3 in column B you can use

=AVERAGEIF(B:B,0.3,A:A)

mgoto125
04-03-2012, 11:53 AM
Thanks for your input everyone, with your help and advice I solved this using the following

=AVERAGEIF(A1:A34,">2000")

I works and so simple, thanks.