vba. about looping to optimize code.

[montecarlo2012]

Hi All.
Working on

Sub j()
    Range("B2").Formula = "=Countif(Q2:WAK2)"
    Range("C2").Formula = "=Max(Q2:WAK2)"
    Range("D2").Formula = "=Countif(Q2:WAK2, 0)"
    Range("E2").Formula = "=COUNTIF(Q2:WAK2, 1)"
    Range("F2").Formula = "=COUNTIF(Q2:WAK2, 2)"
    Range("G2").Formula = "=COUNTIF(Q2:WAK2, 3)"
    Range("H2").Formula = "=COUNTIF(Q2:WAK2, 4)"
    Range("I2").Formula = "=COUNTIF(Q2:WAK2, 5)"
    Range("J2").Formula = "=COUNTIF(Q2:WAK2, 6)"
    Range("K2").Formula = "=Average(Q2:WAK2)"
End Sub

How is possible to make shorter.
thanks.

 

[Michael M]

Hardly worth the effort though !!
Note the error in the formula on line 2 of your code !

Sub j()
Dim n As Integer, c As Integer
    Range("B2").Formula = "=Countif(Q2:WAK2)" 
    Range("C2").Formula = "=Max(Q2:WAK2)"
    Range("K2").Formula = "=Average(Q2:WAK2)"
    n = 0
    For c = 4 To 11
        Cells(2, c).Formula = "=Countif(Q2:WAK2," & n & ")"
        n = n + 1
    Next c
End Sub

 

[Peter_SSs]

Noting Michael's comment about an invalid formula in your post, I've replaced it with a guessed formula. You can enter them all at once with a one-liner like this

Sub j_1()
  Range("B2:K2").Formula = Array("=Countif(Q2:WAK2,-1)", "=Max(Q2:WAK2)", "=Countif(Q2:WAK2, 0)", "=COUNTIF(Q2:WAK2, 1)", "=COUNTIF(Q2:WAK2, 2)", "=COUNTIF(Q2:WAK2, 3)", "=COUNTIF(Q2:WAK2, 4)", "=COUNTIF(Q2:WAK2, 5)", "=COUNTIF(Q2:WAK2, 6)", "=Average(Q2:WAK2)")
End Sub

 

[montecarlo2012]

Hardly worth the effort though !!
Note the error in the formula on line 2 of your code !

Sub j()
Dim n As Integer, c As Integer
    Range("B2").Formula = "=Countif(Q2:WAK2)"
    Range("C2").Formula = "=Max(Q2:WAK2)"
    Range("K2").Formula = "=Average(Q2:WAK2)"
    n = 0
    For c = 4 To 11
        Cells(2, c).Formula = "=Countif(Q2:WAK2," & n & ")"
        n = n + 1
    Next c
End Sub

Hello, Michael M: thanks for your time
when I run the code this is what happen

 

[montecarlo2012]

Thank you Peter SSs for your time
this is what I get from your code

and this is what I am looking for

the difference is the "B" I didn't get any return on "count"
Thank you Peter

 

[Marc L]

when I run the code this is what happen

Hi, as yet stated in post #2 your B2 formula is not valid ! Remove the codeline or correct the formula …​

 

[Peter_SSs]

the difference is the "B" I didn't get any return on "count"

That is because we do not know exactly what formula you want in column B. You showed us "=Countif(Q2:WAK2)" but that is not a valid formula.

 

[Dan_W]

Not wanting to interfere in an already busy thread, I would just make an observation that there is one further difference between preferred/actual outcomes that doesn't appear to be getting picked up (and it's a very minor one) - the layout.

In the actual outcome, the average is in column K, whereas it appears from screen capture 2 that you prefer it to be in column L. Your original code actually puts the AVERAGE formula in column K, and Peter_SSs's array method properly reflects that. If, however, you want it to appear in Column L (as your screen capture suggests), this can be easily accomplished by slightly adjusting the range and including an empty string:

Range("B2:L2").Formula = Array("=Countif(Q2:WAK2,-1)", "=Max(Q2:WAK2)", "=Countif(Q2:WAK2, 0)", "=COUNTIF(Q2:WAK2, 1)", "=COUNTIF(Q2:WAK2, 2)", "=COUNTIF(Q2:WAK2, 3)", "=COUNTIF(Q2:WAK2, 4)", "=COUNTIF(Q2:WAK2, 5)", "=COUNTIF(Q2:WAK2, 6)", "", "=Average(Q2:WAK2)"

Again, like Peter's, this code stills include the problematic formula(s), which I assume is being addressed.

 

[montecarlo2012]

Thank you Peter SSs, you are right I must have

►Range("B2").Formula = "=COUNT(Q2:WAK2)"◄ ["sorry Peter"]

Dan_W Thank you. interfere ? never, you are more than welcome
and thank you for your idea.

Now everything is working.

Thank you for the great lesson all of you.

 

[Marc L]

According to your initial post and your corrected formula so maybe :​

Sub Demo1()
    Const A = "Q2:WAK2", C = "=COUNTIF(" & A & ","
    [B2:K2].Formula = Split(Replace(Replace("=COUNT(¤) =MAX(¤) #,0) #,1) #,2) #,3) #,4) #,5) #,6) =AVERAGE(¤)", "¤", A), "#", C))
End Sub