How to use Do While to calculate average

[Saran]

Hi,

i'm trying to create a macro using Do While statement for below case. This is to calculate the average value

OrdPrefix OrdNo ItemNo Price Cost AVERAGE SE 262 1 2.45 2.956 SE 262 2 2.45 2.956 SE 262 3 2.45 2.956 SE 262 4 2.45 2.956 SE 262 5 2.45 2.956 SE 262 6 2.45 2.956 BE 261 1 2.51 3.15917 BN 251 1 3.41 3.21912 HN 145 1 2.45 1.459 HN 145 2 2.45 1.459 HN 145 3 2.45 1.459

The formula should be Average Price- Average Cost = answer
eg. for Order SE 262
((2.45 + 2.45+ 2.45 + 2.45 + 2.45 +2.45) / 6) - ((2.956 +2.956 + 2.956 + 2.956 + 2.956 +2.956) / 6) = -0.506

I have try below code but doesn't work
ActiveSheet.Cells(2, 6)

myCount = 1

Do While ActiveSheet.Cells(myCount, myCol).FormulaR1C1 = "=IF RC[-5]:[-5] AND RC[-]:[-4] AND RC[-3]:[-3]" Then

ActiveSheet.Cells(2, myCol).FormulaR1C1 = "=RC([-2]-RC[-1])/myCount"
Else
ActiveSheet.Cells(2, myCol).FormulaR1C1 = "=RC[-2]-RC[-1]"

myCount = myCount + 1
Loop

[Teeroy]

Why when you have the built in function to get the value in VBA
[vba]WorksheetFunction.Average(Range("G4:G8"))[/vba]
or to put a formula in a cell[vba]Range("A1").formula = "=Average(G4:G8)"[/vba]

[Saran]

Sorry, I didn't get what you mean. Can you ask or explain in detail.

Kindly let me know if you need more information.


Regards,
Saran

[Teeroy]

If you attach a sample workbook with some example data (doesn't have to be real) I'll add it directly to show you.

[Saran]

i'm confuse. what shoild i provide to you now. resend again?