unable to make the formula dynamic based on column value using loops in macro

Question

This question is related to automating a formula for dynamic range using Macro in excel.

I have 2 columns "A" and "B".
Column A with Application IDs. Application ID in column "A" change dynamically after some iterations i.e. have 18 count, then 43, then 15 and so on...
Column B has corresponding 0s and 1s.

I'm calculating a binomial distribution formula based on the count values using 18 B values, then 43 B values, then 15 B values and so on.

If the Application ID in two rows doesn't match then a formula should be calculated. The formula contains 2-19 row values, than 20-62 row values, than 63-77 row values and so on...

I want it to calculate for 109972 cells. Looking for a macro which can do this.

First formula:

=IF(A19<>A20,BINOM.DIST(COUNTIF($B$2:B19,0),COUNT($B$2:B19),COUNTIF($B$2:B19,0)/COUNT($B$2:B19),FALSE),"")

Second Formula:

=IF(A62<>A63,BINOM.DIST(COUNTIF($B$20:B62,0),COUNT($B$20:B62),COUNTIF($B$20:B62,0)/COUNT($B$20:B62),FALSE),"")

Third Formula (and so on has to calculated)

=IF(A77<>A78,BINOM.DIST(COUNTIF($B$63:B77,0),COUNT($B$63:B77),COUNTIF($B$63:B77,0)/COUNT($B$63:B77),FALSE),"")

You can use this giant formuala in D2 (and copy down) `=IF(A2<>A3,BINOM.DIST(COUNTIF(OFFSET($B$2:B2,IFERROR(MATCH(0,$D1:D$2,-1),0),0,ROW()-IFERROR(MATCH(0,$D1:D$2,-1),0)-1),0),COUNT(OFFSET($B$2:B2,IFERROR(MATCH(0,$D1:D$2,-1),0),0,ROW()-IFERROR(MATCH(0,$D1:D$2,-1),0)-1)),COUNTIF(OFFSET($B$2:B2,IFERROR(MATCH(0,$D1:D$2,-1),0),0,ROW()-IFERROR(MATCH(0,$D1:D$2,-1),0)-1),0)/COUNT(OFFSET($B$2:B2,IFERROR(MATCH(0,$D1:D$2,-1),0),0,ROW()-IFERROR(MATCH(0,$D1:D$2,-1),0)-1)),FALSE),"-") ` or you write yourself a VBA script. — Pᴇʜ, Jun 13 '17 at 11:06
Thanks for the big formula but above formula is having error especially with match onward... — mbajpai, Jun 13 '17 at 11:30
@mbajpai it worked, at least I got the same results as in your screenshot. — Pᴇʜ, Jun 13 '17 at 11:40

CMArg · Accepted Answer · 2017-06-13T11:43:25.540

If your data is in a sheet named Data, add a command button and then following code. You should check the binomial parameters, cause I'm not used to them.

Private Sub CommandButton1_Click()
    Dim lTrialNumber As Long
    Dim lFailNumber As Long
    Dim lLastRow As Long
    Dim i As Long

    lLastRow = Worksheets("Data").Cells(1, 1).End(xlDown).Row
    lTrialNumber = 0
    lFailNumber = 0

    For i = 2 To lLastRow 'if data start in row 2. Row 1 for Titles
        If Worksheets("Data").Cells(i + 1, 1) <> Worksheets("Data").Cells(i, 1) Then
            lTrialNumber = lTrialNumber + 1
            If Worksheets("Data").Cells(i, 2) = 0 Then
                lFailNumber = lFailNumber + 1
            End If
            Worksheets("Data").Cells(i, 4) = WorksheetFunction.BinomDist(lFailNumber, lTrialNumber, lFailNumber / lTrialNumber, False)
            lTrialNumber = 0
            lFailNumber = 0
        Else
            lTrialNumber = lTrialNumber + 1
            If Worksheets("Data").Cells(i, 2) = 0 Then
                lFailNumber = lFailNumber + 1
            End If
        End If
    Next
End Sub

Never use `Interger` unless you want to communicate with old APIs. Always user `Long` instead. Especially when using it for row counts. Excel has more rows than `Integer` can handle. See [here](https://stackoverflow.com/a/26409520/3219613) for more info. — Pᴇʜ, Jun 13 '17 at 11:42
@mbajpai be polite and mark as accepted (thats the way we "get paid" here...). Or comment on the problems code might have. — CMArg, Jun 13 '17 at 14:16

unable to make the formula dynamic based on column value using loops in macro

1 Answers1