-1

enter image description here

enter image description here

This is what would be the output

enter image description here

I have this formula in cell "C1" To average the values in column2 with respected to column1 values.It gives what I need to do. But I want to get this work into a vba code.

Can anyone help to put this into a code. Is it possible when we have reference cells in the formula?? or do we need to go to more complex code??

=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
  • Record it with the macro recorder? – findwindow Apr 29 '16 at 22:29
  • 1
    The macro recorder will only help if you want to *enter the formula* by using VBA. I think, he wants to *perform the calculation* in VBA; that's not possible with the macro recorder. A possible solution which is analogous to the formula approach would be to use the `WorksheetFunction` object: https://msdn.microsoft.com/en-us/library/office/ff834434.aspx – Leviathan Apr 29 '16 at 22:47
  • 1
    Possible duplicate of [Average column value based on other column value vba code](http://stackoverflow.com/questions/36921899/average-column-value-based-on-other-column-value-vba-code) – vacip Apr 30 '16 at 16:30
  • @vacip It's not just a duplicate, it's the same person with the same question. Apparently not happy with the Answer, so trying again... – Cindy Meister Apr 30 '16 at 19:40

1 Answers1

3

Here is one way to do C1's calculation is VBA: 

Sub DoingC1Job()
    Dim s As String, s2 as string
    s = "=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))),??)"
    s = Replace(s, "??", Chr(34) & Chr(34))
    s2 = Evaluate(s)
    MsgBox s2
End Sub

The ?? is just an easy way to get the double-quotes into the string.

As improved by Leviathan:

Sub DoingC1Job()
    Dim s As String, v As Variant
    s = "=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))),"""")"
    v = Evaluate(s)
    MsgBox v
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • S Hi Thanks, it gives the answer for my first set, can we copy "S" down to other cells and get the answers in the Column C at respective cells.?? please see the picture? – Kuma Apr 29 '16 at 23:07
  • 1
    `TEXT(,)` replaces `""` in a string formula nicely as well. –  Apr 29 '16 at 23:12
  • Yes, but we would modify **s** in a loop to change the **1** and **2** cell references – Gary's Student Apr 29 '16 at 23:12
  • @ Gary's Thank you, I will go from that. – Kuma Apr 29 '16 at 23:22
  • "*The ?? is just an easy way to get the double-quotes into the string.*" -- Why not just use `s = "=IF(A2<>A1, ... <>A2,)),0))),"""")"`, why the `Replace()`? – Leviathan Apr 30 '16 at 09:53
  • @Leviathan My personal mental problem...........I have trouble keeping track of the number of double quotes – Gary's Student Apr 30 '16 at 11:57
  • @Gary's Student: Mhmm... well, I don't think it is a good idea to show this kind of workaround in an answer. Since it will only work if no other question marks are present in the string, this can easily introduce new errors. If you get confused by doubling all quotes (you could use the search&replace feature of your favourite text editor to automate this), instead of replacing them by question marks, directly replace them by `" & Chr(34) & "`. Also see http://stackoverflow.com/a/9024764/6216216 for handling double quotes in strings. – Leviathan Apr 30 '16 at 12:59
  • @Leviathan I am forced to agree with you and I will edit my answer.........thank you for your help......... – Gary's Student Apr 30 '16 at 13:01
  • Thanks, now it's +1. :) – Leviathan Apr 30 '16 at 13:15