-5

I have three columns "SAMPLE ID" Results , Average

Eg:

SAMPLE ID 
1201
1201
1201
1201
1300
1300
1300
1300
1400
Results 
400
490
340
400
400
490
233
400
290

I used the following formula to get the average in column 3. I want to this to be done in vba. My data set is long (only few are shown)

  1. Can I fill this formula down to other cell using vba (even without typing it in the first cell)

2.can we define this as a string and copy it down??? all the helps are appriciated. or any other way of coding to this Thanks

= IF(A2<>A1,AVERAGEIF(A2:INDEX($A2:INDEX(A:A,MATCH(1E+99,A:A)+1),MATCH(TRUE,(INDEX($A2:INDEX(A:A,MATCH(1E+99,A:A)+1)<>A2,)),0)),A2,B2:INDEX($B2:INDEX(B:B,MATCH(1E+99,A:A)+1),MATCH(TRUE,(INDEX($A2:INDEX(A:A,MATCH(1E+99,A:A)+1)<>A2,)),0))),"")
Community
  • 1
  • 1
Kuma
  • 57
  • 1
  • 9
  • This exact question was [asked and has been answered](http://stackoverflow.com/questions/36948413/add-a-reference-cell-formula-in-to-a-code) within the last 12 hours. –  Apr 30 '16 at 11:52
  • It is not exact Q, don't judge with appearance, read the text. – Kuma Apr 30 '16 at 13:29

1 Answers1

0

try this

Sub main()
With Worksheets("averages") '<== change it as per your actual worksheet name
    Range("C1:C" & .Cells(.Rows.Count, 1).End(xlUp).Row).formula = "= IF(A2<>A1,AVERAGEIF(A2:INDEX($A2:INDEX(A:A,MATCH(1E+99,A:A)+1),MATCH(TRUE,(INDEX($A2:INDEX(A:A,MATCH(1E+99,A:A)+1)<>A2,)),0)),A2,B2:INDEX($B2:INDEX(B:B,MATCH(1E+99,A:A)+1),MATCH(TRUE,(INDEX($A2:INDEX(A:A,MATCH(1E+99,A:A)+1)<>A2,)),0))),"""")"
End With
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28
  • @Vasily, The code is perfect. Thank you, can we modify it not to display the formula in the respective cells in excel? I tried this but not doing the work Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rng As Range For Each rng In Target.Cells If rng.HasFormula Then ActiveSheet.Protect Exit Sub Else ActiveSheet.Unprotect End If Next rng End Sub – Kuma Apr 30 '16 at 13:24