Saran
12-06-2012, 09:44 PM
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
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